PostgreSQL: SIMILAR TO operator

SIMILAR TO operator

The SIMILAR TO operator returns true if its pattern matches the given string otherwise returns false. It is similar to LIKE operator, except that it interprets the pattern using the SQL standard's definition of a regular expression.

Syntax:

string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]

Using the SIMILAR TO operator

• SIMILAR TO operator succeeds only if its pattern matches the entire string; this is unlike common regular expression behavior where the pattern can match any part of the string. 
• It uses _ and % as wildcard characters denoting any single character and any string.
• SIMILAR TO supports pattern-matching metacharacters taken from POSIX regular expressions :
  • | denotes alternation (either of two alternatives).
  • * denotes repetition of the previous item zero or more times.
  • + denotes repetition of the previous item one or more times.
  • ? denotes repetition of the previous item zero or one time.
  • {m} denotes repetition of the previous item exactly m times.
  • {m,} denotes repetition of the previous item m or more times.
  • {m,n} denotes repetition of the previous item at least m and not more than n times.
  • Parentheses () can be used to group items into a single logical item.
  • A bracket expression [...] specifies a character class, just as in POSIX regular expressions.

Example:

'xyz' SIMILAR TO 'xyz'      true
'xyz' SIMILAR TO 'x'        false
'xyz' SIMILAR TO '%(y|a)%'  true
'xyz' SIMILAR TO '(y|z)%'   false

Here is sample table countries

Example :

If we want to extract those rows which contain the country_name starting with the letter 'M' from countries table, the following statement can be used.

postgres=# SELECT country_name
postgres-# FROM countries
postgres-# WHERE country_name SIMILAR TO 'M%'='t';
 country_name
--------------
 Mexico
(1 row)

Example:

If we want to extract those rows which contain the country_name 'Mozhe' from countries table , the following statement can be used.

postgres=# SELECT country_name
postgres-# FROM countries
postgres-# WHERE country_name SIMILAR TO 'Italy'='t';
 country_name
--------------
 Italy
(1 row)

Example:

If we want to extract those rows which contain the country_name ending with the letter 'y' or 'l' from countries table, the following statement can be used.

postgres=# SELECT country_name
postgres-# FROM countries
postgres-# WHERE country_name SIMILAR TO '%(y|l)'='t';
 country_name
--------------
 Brazil
 Germany
 Israel
 Italy
(4 rows)

Example:

If we want to extract those rows which contain the country_name with the letter 'k' or 'y' in any position from countries table , the following statement can be used.

postgres=# SELECT country_name
postgres-# FROM countries
postgres-# WHERE country_name SIMILAR TO '%(k|y)%'='t';
 country_name
--------------
 Germany
 Denmark
 Egypt
 Italy
(4 rows)

POSIX Regular Expressions

POSIX regular expressions provide a more powerful means for pattern matching than the LIKE and SIMILAR TO operators. The following table shows the regular Expression Match Operators.

Example:

postgres=# SELECT 'string' ~ '^s' ;
 ?column?
----------
 t
(1 row)

Example:

postgres=# SELECT 'string' ~ '(n|r)' ;
 ?column?
----------
 t
(1 row)

Example:

postgres=# SELECT 'string' ~ '^(r|n)';
 ?column?
----------
 f
(1 row)

Example:

postgres=# SELECT 'string' ~ '.*string.*';
 ?column?
----------
 t
(1 row)

Example:

postgres=# SELECT 'string' ~* '.*String.*';
 ?column?
----------
 t
(1 row)

Example:

postgres=# SELECT 'string' !~ '.*String.*';
 ?column?
----------
 t
(1 row)

Example:

postgres=# SELECT 'string' !~* '.*mndsi.*';
 ?column?
----------
 t
(1 row)