I try to only query lowercase results but LIKE is not case insensitive.
Here is an example:
SELECTCITYFROMTARGETSWHERECITY RLIKE '^b.*n$'the result is
BOSTONbostonI want to only keep the 'boston', but I don't know how to do it. I checked this old answer:
Case sensitive RLIKE
But, the statement CAST(CITY AS BINARY) RLIKE '^b.*n$' doesn't work in PostgreSQL.
Best Answer
RLIKE is used in MySQL. The regular expression match operator in Postgres is ~. (Neither is standard SQL.)~ is case-sensitive by default.~* is the case-insensitive variant.
This gets you 'boston' but not 'Boston':
SELECT cityFROM targetsWHERE city ~ '^b.*n$';See:
- Difference between LIKE and ~ in Postgres
but
LIKEis not case insensitive.
You may have tripped over the double negative there, but "not case insensitive" (i.e. case-sensitive) is what you want. And it's also how LIKE works in Postgres (or standard SQL):
SELECT cityFROM targetsWHERE city LIKE 'b%n';If you want case-insensitive, use ILIKE in Postgres.
db<>fiddle here
But MySQL does it differently:
db<>fiddle here
See:
- How do you force mysql LIKE to be case sensitive?