Regular expression in PostgreSQL LIKE clause
Solution 1
Like @a_horse commented, you would have to use the regular expression operator ~
to use bracket expressions.
But there's more. I suggest:
SELECT *
FROM tbl
WHERE value ~ '^00[^0]'
^
... match at start of string (your original expression could match at any position).
[^0]
... a bracket expression (character class) matching any character that is not 0
.
Or better, yet:
SELECT *
FROM tbl
WHERE value LIKE '00%' -- starting with '00'
AND value NOT LIKE '000%' -- third character is not '0'
Why? LIKE
is not as powerful, but typically faster than regular expressions. It's probably substantially faster to narrow down the set of candidates with a cheap LIKE
expression.
Generally, you would use NOT LIKE '__0'
, but since we already establish LIKE '00%'
in the other predicate, we can use the narrower (cheaper) pattern NOT LIKE '000'
.
Postgres can use a simple btree index for the left-anchored expressions value LIKE '00%'
(important for big tables), while that might not work for a more complex regular expression. The latest version of Postgres can use indexes for simple regular expressions, so it might work for this example. Details:
Solution 2
PostgreSQL's LIKE
operator doesn't support [charlist], however SIMILAR TO
does.
check HERE for a comprehensive list across DBs
Comments
-
borarak over 4 years
I'm stuck with a simple regular expression. Not sure what I'm missing. A little rusty on regex skills.
The expression I'm trying to match is:
select * from table where value like '00[1-9]%' -- (third character should not be 0)
So this should match
'0090D0DF143A'
(format: text) but it's NOT!