SIMILAR TO operator

Description

An expression with the SIMILAR TO operator returns TRUE or FALSE depending on whether its pattern overlaps with the given text string.

This operator is similar to the LIKE operator, but in this case the pattern is interpreted as a regular expression. As with LIKE, an expression with SIMILAR TO is checked successfully only if the pattern is applied to the entire string. This differs from the usual behaviour of regular expressions, where the pattern can be applied to any part of the string.

Regular expressions use the syntax RE2.

You can also use opposing expressions with opposite meanings
<string> NOT SIMILAR TO <pattern> and NOT string SIMILAR TO <pattern>.

Expressions with the SIMILAR TO operator are completely synonymous with expressions with the ~ operator.

Examples

SELECT
    'Tengri' SIMILAR TO 'TNGRi'  AS result_1, -- false expected
    'Tengri' SIMILAR TO 'T.*'    AS result_2,
    'Tengri' SIMILAR TO 'T.+i'   AS result_3,
    'TNGRi'  SIMILAR TO 'T.+i'   AS result_4,
    'T.+i'   SIMILAR TO 'T\.\+i' AS result_5;
+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 |
+----------+----------+----------+----------+----------+
| false    | true     | true     | true     | true     |
+----------+----------+----------+----------+----------+
SELECT
    'Tengri' NOT SIMILAR TO 'TNGRi'  AS result_1,
    NOT 'T.{3}i' SIMILAR TO 'T.{3}i' AS result_2;
+----------+----------+
| result_1 | result_2 |
+----------+----------+
| true     | true     |
+----------+----------+

See also