PostgreSQL String Matching: LIKE vs. ~ for Efficient Regular Expression Queries?

Regular Expressions in PostgreSQL LIKE Clause: Using LIKE vs. ~ Operator

In PostgreSQL, you can utilize regular expressions to search for specific patterns within data. However, if you're encountering issues with your expressions, it's essential to understand the nuances and limitations.

The Challenge: Third Character Not Matching

Consider the following query:

SELECT *
FROM table
WHERE value LIKE '00[1-9]%'
-- (third character should not be 0)

Despite its intention to exclude any value with a third character of '0', this query will not match that criteria. Instead, it matches any value beginning with '00'.

The Solution: Using the ~ Operator

To utilize bracket expressions effectively, we must employ the regular expression operator ~:

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

^ ... denotes the start of the string, ensuring the pattern matches only at the beginning.
¹ ... represents a character class that matches anything but '0'.

An Optimized Approach

For optimal performance, consider breaking the query into two LIKE expressions:

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

LIKE is less potent than regular expressions but typically faster. By limiting the candidates with the economical LIKE expression, we improve the overall query speed.

Additionally, it's crucial to utilize left-anchored expressions like value LIKE '00%' as they can leverage indexes in PostgreSQL. This is particularly beneficial for large tables, while the more complex regular expression might not support indexing.

The above is the detailed content of PostgreSQL String Matching: LIKE vs. ~ for Efficient Regular Expression Queries?. For more information, please follow other related articles on the PHP Chinese website!