I have a name column and I need to check whether it starts with letters A to P, or the first 2 letters of the name are AA to AE.
I tried this:
select name
from table
where substring(name,1,1) ~* '^[A-P]'
or substring(name,1,2) ~* '^[AA-AE]';
This didn't work.
Don't apply functions on the column in the filter expression if you can avoid it. That's not "sargable" and makes it more expensive, or much more expensive if it disables an otherwise applicable index. Simply:
WHERE name ~ '^[A-P]'
See:
The regular expression match with ~ is case-sensitive. Use ~* instead if you need case-insensitive.
The second condition ("starts with AA to AE") is a subset, so it would add nothing but cost after OR. If you need it separately:
WHERE name ~ '^A[A-E]'
whether it starts with letters A to P
WHERE SUBSTRING(UPPER(name) FROM 1 FOR 1) BETWEEN 'A' AND 'P'
or the first 2 letters of the name starts with AA to AE.
OR SUBSTRING(UPPER(name) FROM 1 FOR 2) BETWEEN 'AA' AND 'AE'
To make it Case-Insensitive use UPPER function. Another function you could use is LEFT .
SELECT mt.*
FROM myTable mt
WHERE SUBSTRING(UPPER(name) FROM 1 FOR 1) BETWEEN 'A' AND 'P'
OR SUBSTRING(UPPER(name) FROM 1 FOR 2) BETWEEN 'AA' AND 'AE';
