Regular expression in PostgreSQL LIKE clause

by

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:

More Related Contents:

  1. Does PostgreSQL support “accent insensitive” collations?
  2. Difference between LIKE and ~ in Postgres
  3. PostgreSQL “Column does not exist” but it actually does
  4. Is SELECT or INSERT in a function prone to race conditions?
  5. Any downsides of using data type “text” for storing strings?
  6. Index for finding an element in a JSON array
  7. Change PostgreSQL columns used in views
  8. How to set auto increment primary key in PostgreSQL?
  9. List columns with indexes in PostgreSQL
  10. PostgreSQL IF statement
  11. Split given string and prepare case statement
  12. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  13. Double colon `::` notation in SQL
  14. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  15. I keep getting the error “relation [TABLE] does not exist”
  16. Export specific rows from a PostgreSQL table as INSERT SQL script
  17. PostgreSQL Where count condition
  18. How to write a constraint concerning a max number of rows in postgresql?
  19. Postgres constraint for unique datetime range
  20. SQL Transpose Rows as Columns
  21. oracle — Split multiple comma separated values in oracle table to multiple rows
  22. PostgreSQL create table if not exists
  23. Regular expression to match common SQL syntax?
  24. Count cumulative total in Postgresql
  25. Prepared Statement on Postgresql in Rails
  26. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  27. PostgreSQL: Full Text Search – How to search partial words?
  28. How to bulk insert only new rows in PostreSQL
  29. RegEx to Detect SQL Injection
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment