sql - Regular expression in PostgreSQL LIKE clause

Question

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!

Answer 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:

• Difference between LIKE and ~ in Postgres