43

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!

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
borarak
  • 1,130
  • 1
  • 13
  • 24
  • 3
    The SQL `LIKE` operator does not support regular expression. You need to use `similar to` or the `~` operator: http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP –  Jun 23 '14 at 14:26

2 Answers2

56

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Maybe `'^00[^0]'` or `'^00[^0].*'` instead of `'^00[^0]%'` in the first query? Otherwise it does not match the example `0090D0DF143A` – Ihor Romanchenko Jun 23 '14 at 15:01
  • 1
    It does indeed use indexes for this regex, see: http://sqlfiddle.com/#!15/7019f/1 – Nick Barnes Jun 23 '14 at 15:44
  • 1
    @NickBarnes: In this case, the first query is about as fast as the second. Without matching index, the second will be substantially faster. I tested, building on your fiddle: http://sqlfiddle.com/#!15/d9b43/5 Performance tests on sqlfiddle are not always reliable. I ran the tests in a local 9.3 setup. – Erwin Brandstetter Jun 23 '14 at 16:13
  • @Erwin Wow, quite a difference! I'll have to remember that one ;) – Nick Barnes Jun 23 '14 at 16:33
4

PostgreSQL's LIKE operator doesn't support [charlist], however SIMILAR TO does.

check HERE for a comprehensive list across DBs

Community
  • 1
  • 1
sunbabaphu
  • 1,473
  • 1
  • 10
  • 15