How Can I Efficiently Match Strings Starting with '00' and a Non-Zero Third Digit in PostgreSQL?

Regular Expressions in PostgreSQL LIKE Clause

Your expression is intended to match strings starting with '00' and followed by a digit between 1 and 9, but excluding strings where the third character is '0'. The incorrect expression is:

select * from table where value like '00[1-9]%'

Incorrect usage of LIKE Operator:

Regular expression operators in PostgreSQL use the tilde (~) instead of LIKE. To use bracket expressions, you need to change the operator to ~.

Improved Regex:

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

• ^: Matches the start of the string

Alternative using LIKE:

Instead of a regular expression, you can use the LIKE operator combined with negation:

SELECT *
FROM   table
WHERE  value LIKE '00%'       -- Starts with '00'
AND    value NOT LIKE '000%'  -- Excludes third character '0'

This approach is often faster than regular expressions for simple patterns.

Index Optimization:

Postgres supports indexes for left-anchored LIKE expressions like '00%'. Indexing can significantly improve performance on large tables. Regular expressions may not always utilize indexes, so it's worth considering the LIKE alternative for better indexing opportunities.

The above is the detailed content of How Can I Efficiently Match Strings Starting with '00' and a Non-Zero Third Digit in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!