postgres query a string not starting with a given set of characters using LIKE or ILIKE keywords-CodePudding

Is it possible in Postgresql to query a string that does not start with vowels (or a given set of characters) using only LIKE or ILIKE keyword?

For example I was playing with this sample DB and was trying to extract from Customer the first_names that do not start with vowels:

SELECT first_name
FROM Customer
WHERE first_name NOT ILIKE '[aeiou]%'
ORDER BY first_name;

This query however does not work and I get results like Aaron, Adam etc. The problem is in the square brackets because the condition NOT ILIKE 'a%' works.

I know there are previous answers to similar questions but 1) they are not in postgresql and 2) they use regular expressions or substringing whereas I'd like to know if a solution using LIKE/ILIKE exists

CodePudding user response：

AFAIK, LIKE/ILIKE do not support regex syntax, which is what your're trying to use in the provided example. LIKE/ILIKE only supports % and _ special symbols and those don't do what you need them to in a single expression.

You could probably get away with something like

SELECT first_name
FROM Customer
WHERE first_name NOT ILIKE 'a%' AND first_name NOT ILIKE 'e%' AND  first_name NOT ILIKE 'i%' AND  first_name NOT ILIKE 'o%' AND  first_name NOT ILIKE 'u%'
ORDER BY first_name;

...but it'd take a very good reason to do it that way, rather than using regex.

CodePudding user response：

In addition you can use ^ symbol (beginning of word (string))

SELECT first_name
FROM Customer
WHERE not first_name ~* '^[aeiou]'
ORDER BY first_name;

CodePudding user response：

LIKE (and it's Postgres extension ILIKE) does not support regular expressions in SQL.

If you want to use a regex, use you can use the standard-compliant similar to or the Postgres specific ~* operator

SELECT first_name
FROM Customer
WHERE not first_name ~* '^[aeiou]*'
ORDER BY first_name;