Skip to content

postgres patern matching like similar regexp

Jump to bottom
ghdrako edited this page May 1, 2024 · 1 revision 
SELECT *
FROM   employees
 WHERE  email LIKE '%@gmail.com'; 

SELECT *
FROM users
WHERE
    email LIKE '%@gmail.com'
    OR email LIKE '%@yahoo.com'

SIMILAR - match against multiple patterns

Like LIKE, SIMILAR TO uses _ and % as wildcard characters denoting any single character and any string, respectively.

SELECT *
FROM users
WHERE email SIMILAR TO '%@gmail.com|%@yahoo.com'

RegExp

SELECT *
FROM users
WHERE email ~ '@gmail\.com$|@yahoo\.com$'

ANY to match against array of patterns

PostgreSQL has an ANY operator that is used to compare a scalar value with a set of values returned by a subquery. And in this case, the sub-query is an array for patterns to match.

SELECT *
FROM users
WHERE email ~ ANY(ARRAY['@gmail\.com$', '@yahoo\.com$'])

This query uses the ANY operator to match against an array of patterns. If an email matches any of the patterns, the condition will be true. This approach is easier to work with from a host language such as Python:

with connection.cursor() as cursor:
    cursor.execute('''
        SELECT *
        FROM users
        WHERE email ~ ANY(ARRAY%(patterns)s)
    ''' % {
        'patterns': [
            '@gmail\.com$',
            '@yahoo\.com$',
        ],
    })

Unlike the previous approach that used SIMILAR TO, using ANY you can bind a list of patterns to the variable.

Test

Clone this wiki locally