SIMILAR TO Predicate
Use SIMILAR TO in a WHERE clause to search for a regular expression pattern in a column.
The SIMILAR TO predicate has the following syntax:
expression [NOT] SIMILAR TO pattern [WITH CASE | WITHOUT CASE]
[ESCAPE escape_character]
where
expression
Is a column name or an expression containing string functions
pattern
Specifies the pattern to be matched.
The pattern operators are:
literal
Matches itself.
[ … ]
Matches a set of characters.
A character set expression can contain a list of named character classes, individual characters, or inclusive ranges specified with a hyphen (-). Character class names are themselves enclosed with [: and :].
Supported class names are: ALPHA, UPPER, LOWER, DIGIT, ALNUM, SPACE, and WHITESPACE.
[ … ^ … ]
Matches one character in the set preceding the ^ unless de-selected by the set after it. To abbreviate a range, use a hyphen (-). For example:
[[:LOWER:]^aeiou] matches the lower case consonants.
_
Matches any single character.
%
Matches zero or more characters.
\ …
Escapes the character that follows if it is a meta character.
( … )
Treats pattern elements as one unit.
… | …
Matches one of the alternates.
Suffix Operators--The following operators can be appended to the end of the preceding operators for further modification:
{n}
Matches n occurrences of the preceding pattern element.
{n,}
Matches n or more occurrences of the preceding pattern element.
{n,m}
Matches between n and m occurrences of the preceding pattern element.
?
Matches 0 or 1 occurrences of the preceding pattern element. Shorthand for {1,0}.
*
Matches 0 or more occurrences of the preceding pattern element. Shorthand for {0,}.
+
Matches 1 or more occurrences of the preceding pattern element. Shorthand for {1,}.
SIMILAR TO Examples
The SIMILAR TO predicate returns values depending on whether the pattern matches the expression. It functions similarly to the LIKE predicate.
As with LIKE, the SIMILAR TO operator succeeds only if its pattern matches the entire string. This is unlike common regular expression practice, wherein the pattern can match any part of the string. Like LIKE, SIMILAR TO uses the wildcard characters _ (denoting a single character) and % ( denoting a string).
The SQL standard SIMILAR TO operator supports sets and has a negation operator that is more powerful:
To match only consonants with LIKE it is necessary to declare:
[BCDFGHJKLMNPQRSTVWXYZbcdfghjklmnpqrstvwxyz]
whereas with SIMILAR TO, it can be reduced to:
[A-Za-z^AEIOUaeiou]
where ^ means NOT THESE VALUES.
To match on items that do not begin with "ii" you could code:
string_1 SIMILAR TO ‘[^iI]{2}%’
where:
The ^ inside the [ ] denotes neither i or I. The number inside the { } denotes two of the characters inside the [ ].
WITH CASE and WITHOUT CASE controls case sensitivity.
The above expression:
string_1 SIMILAR TO ‘[^iI]{2}%’
can be written more concisely as:
string_1 NOT SIMILAR TO ‘ii%’ WITHOUT CASE
To match a more complex pattern such as a telephone number, use code like this:
string_1 SIMILAR TO ‘(+[0-9]{2})?([- ]*[0-9]{3,4}){2,3}’
The ‘(+[0-9]{2})?’ matches an optional country code, followed by from two to three ‘(…){2,3}’ space- or hyphen-separated sets of three to four digit numbers ‘[- ]*[0-9]{3,4}’.