Where column not like multiple values
You almost had the correct syntax. This is exactly what you want:
SELECT * FROM rails_db WHERE username NOT LIKE ALL(ARRAY[my values]);
This is a very nice syntax, but it won't necessarily be fast, especially not if the array is large. However, what you want is hard to optimize for performance, and there is no reason to think uglier syntaxes will be any faster.
I may have found it, but I want to see if this works the way it is supposed to:
SELECT * FROM rails_db WHERE username !~* 'j.*|.*eepy.*';
Your idea of using a regular expression with branches is solid. But in your answer you got the translation of the special character %
in LIKE
patterns wrong.
This:
... WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%';
translates to:
... WHERE username !~ '^j|eepy';
!~
is case sensitive like NOT LIKE
.
Use !~*
to match case insensitive like NOT ILIKE
.
To also exclude strings containing a dot (.
) anywhere and with another example that matches (doesn't match) the end of the string like username NOT LIKE '%end'
:
... WHERE username !~ '^j|end$|eepy|\.';
Probably not very fast either.