How to use RLIKE AND regular expression in case insentive

Question

I try to only query lowercase results but LIKE is not case insensitive.

Here is an example:

SELECT
    CITY
FROM
    TARGETS
WHERE
    CITY RLIKE '^b.*n$'

the result is

BOSTON
boston

I 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.

Answer 1

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 city
FROM   targets
WHERE  city ~ '^b.*n$';

See:

• Difference between LIKE and ~ in Postgres

but LIKE is 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 city
FROM   targets
WHERE  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?