Regular Expression in Postgresql Like Clause

Regular expression in PostgreSQL LIKE clause

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

How to use variables in psql LIKE query regular expression

I figured out a solution that doesn't exactly answer my question, but here's how I got around it.

select articles.title, count(*)
from articles, log
where substring(log.path,10,100) = articles.slug
and log.status = '200 OK'
group by articles.title

Difference between LIKE and ~ in Postgres

~ is the regular expression operator, and has the capabilities implied by that. You can specify a full range of regular expression wildcards and quantifiers; see the documentation for details. It is certainly more powerful than LIKE, and should be used when that power is needed, but they serve different purposes.

Using SIMILAR TO for a regex?

The operator is defined as:

string SIMILAR TO pattern 

so the first parameter is the string that you want to compare. The second parameter is the regex to compare against.

You need:

SELECT '22222' SIMILAR TO '[1-3]{5}';

postgres - using SIMILAR TO in a CASE clause

I have it on good authority (https://stackoverflow.com/a/12459689/1278553) that there is no good reason to use "similar to." From the referenced link:

SIMILAR TO is part of the SQL standard, but it is very odd syntax, and
the only reason PostgreSQL supports it, is to stay standard compliant.
Internally, every SIMILAR TO expression is rewritten with a regular
expression. Therefore, for any given SIMILAR TO expression, there is
at least one regexp doing the same job faster.

On that note, if you change this to a normal regular expression it should even be as simple as this:

select
  r.barcode, 
  case
    when r.barcode ~ '\d{5,8}%\d+%' then 'match'
    else 'noMatch'
  end as matchcolumn
from report r

You don't need to escape the % character with a regex.

If you aren't okay with that pattern occuring in the middle of a string, you may want to anchor the beginning and/or end of the regex:

'^\d{5,8}%\d+%$'

---

Related Topics