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!

The SQL LIKE operator does not support regular expression. You need to use similar to or the ~ operator: postgresql.org/docs/current/static/… — user330315
– user330315, Commented Jun 23, 2014 at 14:26

Erwin Brandstetter · Accepted Answer · 2024-11-01 01:59:48Z

67

Bracket expressions only work for the regular expression operator ~.
And "third character should not be 0" translates to:

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:

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

LIKE is not as versatile, but typically faster than regular expressions. It's probably 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'.

With big tables, index support is the key to performance. Postgres can use a plain B-tree index for left-anchored expressions (like `value LIKE '00%'). In modern Postgres for simple regular expressions, too. See:

Difference between LIKE and ~ in Postgres

Postgres 11 added the "starts with" operator ^@, which is the king of micro-optimization now:

WHERE  value ^@ '00'     -- starting with '00'
AND    value !~~ '000%'  -- third character is not '0'

!~~ being the Postgres operator to implement the SQL construct NOT LIKE. So that's just optional flexing.
But ^@ can make an actual difference. For starters, you can use the bare string without concatenating wildcards as pattern. See:

PostgreSQL LIKE query performance variations

edited Nov 1, 2024 at 1:59

answered Jun 23, 2014 at 14:58

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ihor Romanchenko Over a year ago

Maybe '^00[^0]' or '^00[^0].*' instead of '^00[^0]%' in the first query? Otherwise it does not match the example 0090D0DF143A

Nick Barnes Over a year ago

It does indeed use indexes for this regex, see: sqlfiddle.com/#!15/7019f/1

Erwin Brandstetter Over a year ago

@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: sqlfiddle.com/#!15/d9b43/5 Performance tests on sqlfiddle are not always reliable. I ran the tests in a local 9.3 setup.

Nick Barnes Over a year ago

@Erwin Wow, quite a difference! I'll have to remember that one ;)

Community · Accepted Answer · 2017-05-23 11:53:11Z

6

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

check HERE for a comprehensive list across DBs

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Jun 23, 2014 at 14:55

sunbabaphu

1,4931 gold badge10 silver badges15 bronze badges

4 Comments

user330315 Over a year ago

That's not specific to Postgres. The SQL standard does not define any regex for the LIKE operator.

Erwin Brandstetter Over a year ago

Never use SIMILAR TO.

borarak Over a year ago

Why So @ErwinBrandstetter?

Erwin Brandstetter Over a year ago

@borarak: I linked to my rationale.

Collectives™ on Stack Overflow

Regular expression in PostgreSQL LIKE clause

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related