PostgreSQL pattern LIKE not matching simple example

Question

Hi I don't understand why my pattern does not work. It seems to be the same as in many examples I've seen on the Internet. Can you help?

select city from cities where city like 'ny';

56 rows

select city from cities where city like '[a-z][a-z]';

0 row

Show example input data. But I don't know why you think like supports regex, when the docs state that other functions must be used for that: postgresql.org/docs/9.0/functions-matching.html "many examples I've seen on the internet" are useless without citation, especially if they refer to other types of SQL. — underscore_d
– underscore_d, Commented Mar 9, 2021 at 11:55
Character ranges are not part of the standard LIKE operator, though some SQL dialects offer it as an extension -- most notably T-SQL, the dialect used by SQL Server, which is probably what your examples are using. — Jeroen Mostert
– Jeroen Mostert, Commented Mar 9, 2021 at 11:57

S-Man · Accepted Answer · 2021-03-09 11:56:07Z

3

LIKE is no comparator for Regular Expressions (Documentation)

You should take ~ comparator or SIMILAR TO:

SELECT
    *
FROM cities
WHERE city ~ '[a-z][a-z]';

SELECT
    *
FROM cities
WHERE city SIMILAR TO '[a-z][a-z]';

answered Mar 9, 2021 at 11:56

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

You forgot ^ and $.

Well, I just demonstrated the usage of the the comparators ;) If the TO really need to define start and end, then you are right, of course.

No, you don't need the ^ and $. SIMILAR TO (and LIKE) patterns must match the entire string/field.

Thanks for upvoting. Please don't forget to ACCEPT the question as well if it solves your problem! :)