Regular expression in a PostgreSQL LIKE article

Question

I am stuck with a simple regex. Not sure what I am missing. A little rusty regular expression skills.

I am trying to execute the following expression:

select * from table where value like '00[1-9]%'
-- (third character should not be 0)

So this should match '0090D0DF143A'(format: text), but it is NOT!

Answer 1

As @a_horse commented , you will need to use the regex operator~ to use parenthesized expressions .
But there is more. I suggest:

SELECT *
FROM   tbl
WHERE  value ~ '^00[^0]'

^... ( ).
[^0]... ( ), , 0.

:

SELECT *
FROM   tbl
WHERE  value LIKE '00%'       -- starting with '00'
AND    value NOT LIKE '000%'  -- third character is not '0'

? LIKE , , . , LIKE.

, NOT LIKE '__0', LIKE '00%' , ( ) NOT LIKE '000'.

Postgres btree value LIKE '00%' value LIKE '00%' ( ), . Postgres , . :

• LIKE ~ Postgres

Answer 2

PostgreSQL LIKE [charlist], SIMILAR TO .

DB