SQL Reference - SIMILAR

Example

SELECT
    first_name,
    last_name
FROM
    employees
WHERE
    REGEXP_LIKE (first_name, '^B')
    AND last_name SIMILAR TO '%son%';

Output

FIRST_NAME | LAST_NAME
-----------|----------
Ben        | Johnson
Bill       | Allison

Explanation

The query retrieves employee records where the first name begins with ‘B’ and the last name contains ‘son’. The REGEXP_LIKE function filters on the first_name to get all those beginning with ‘B’, while the SIMILAR TO mechanism filters on last_name to retrieve all containing ‘son’.

Example

SELECT SIMILAR TO '%(aa|bb)%' AS Result
FROM (VALUES ('aa'), ('abcd'), ('bbgh'), ('cc')) AS t (col);

Output

 Result
--------
 true
 false
 true
 false
(4 rows)

Explanation

The SIMILAR TO operator in PostgreSQL is used to check if a string matches a SQL regular expression. The string ’%(aa|bb)%’ is a regular expression that matches any string containing either ‘aa’ or ‘bb’. Thus, the query is checking if each string in the column ‘col’ contains either ‘aa’ or ‘bb’. The result is ‘true’ for ‘aa’ and ‘bbgh’ and ‘false’ for ‘abcd’ and ‘cc’.