SIMILAR TO

Data Types and SubtypesData Definition (DDL) Statements

`SIMILAR TO`

Syntax

string-expression [NOT] SIMILAR TO <pattern> [ESCAPE <escape-char>]

<pattern>     ::= an SQL regular expression
<escape-char> ::= a single character

SIMILAR TO matches a string against an SQL regular expression pattern.Unlike in some other languages, the pattern must match the entire string to succeed — matching a substring is not enough.If any operand is NULL, the result is NULL.Otherwise, the result is TRUE or FALSE.

If a literal pattern is used, and it doesn’t start with a wildcard or other special regex character, SIMILAR TO can use an index.

Escaping Special Characters

To match against a character that is special in regular expressions, that character has to be escaped.There is no default escape character;the user specifies one when needed:

'Peer (Poire)' similar to 'P[^ ]+ \(P[^ ]+\)' escape '\'    -- true
'Pera [Pear]'  similar to 'P[^ ]+ #[P[^ ]+#]' escape '#'    -- true
'Päron-äppledryck' similar to 'P%$-ä%' escape '$'           -- true
'Pärondryck' similar to 'P%--ä%' escape '-'                 -- false

The last line demonstrates that the escape character can also escape itself, if needed.