SQL Reference

SIMILAR TO Operator

Overview
SIMILAR TO is an extension of the LIKE operator and adds more features seen in regular expressions. % and _ remain as wildcard operators, but other supported metacharacters shown here match traditional regular expressions.
SQL
WHERE <Char type> SIMILAR TO <Char type> ...
WHERE <Char type> SIMILAR TO <Char type> ...
﻿
Syntax
Metacharacters supported by SIMILAR TO are shown here.
Metacharacter
Description
%
Repeated wildcard, matches any characters zero or more times. Equivalent to LIKE
_
Wildcard, matches exactly one of any character. Equivalent to LIKE
|
alteration, either of two alternatives (a|b represents a or b)
*
repetition zero or more times
+
repetition one or more times
?
repetition zero or one times
{m}
repetition exactly m times
{m,n}
repetition between m and n times (inclusive)
{m,}
repetition at least m times
()
Logical grouping
[]
A character class, equivalent to character classes in regular expressions. Example: [a-z] is any lower case English letter.
^
Beginning of line anchor as well as negation in character classes
$
End of line anchor
SIMILAR TO also supports escaping these metacharacters with \ as well as certain escape sequences that are supported by C family languages.
Metacharacter
Description
\a
The alert/bell character
\b
The backspace character
\B
A single \ character. Equivalent to \\
\cX
The character with the same 5 lower order bits as X with all zeros as higher order bits
\e
The character with numeric value 27, ESC
\f
Form feed
\n
Newline
\r
Carriage return
\t
Horizontal tab
\uwxyz
Where wxyz is 4 hexadecimal digits. Two characters, one with value numeric wx, and one with numeric value yz
\Uabcdefgh
Where abcdefg is 8 hexadecimal digits. 4 characters, numeric value ab, numeric value cd, numeric value ef, numeric value gh
\v
Vertical tab
\xd….
Where d is any number of hexadecimal digits. If the number of digits is odd, a 0 is added to the left of the sequence. numeric characters with values represented by every group of two symbols from d.
\0
Null character 0
\xy
Where xy are octal digits. The character with numeric value xy.
\xyz
Where xyz are octal digits. The character with numeric value xyz. If x >= 4, 2 characters are returned. Numeric value 1, and numeric value of the lower 8 bits of xyz.
\d
Matches any digit
\s
Matches any whitespace
\w
Matches any alphanumeric character or _
\D
Matches any non-digit
\S
Matches any non-whitespace
\W
Matches any non-alphanumeric that is also not _
\A
The beginning of a line, equivalent to ^
\m, \M, or \y
The boundary of a word. Equivalent to \b in normal regular expressions
\Y
Any position that is not the boundary of a word.
\Z
The end of a line. Equivalent to $ in normal regular expressions
\*
The star character
\+
The plus character
\?
The question mark character
Remarks
Any \ that does not precede a metacharacter (including \), or an escape sequence in the metacharacter table will be ignored
Invalid expressions (unpreceded quantifiers, unclosed parentheses, etc) will result in query errors.
The . is not a metacharacter as it is in traditional regular expressions
Escapes that apply to string literals will still be applied. To match a string containing a single '\', you might need to use '\\\\'.
Examples
SQL
'abc' SIMILAR TO 'abc'          true
'abc' SIMILAR TO 'a'            false
'abc' SIMILAR TO '%(b|d)%'      true
'abc' SIMILAR TO '(b|c)%'       false
'-abc-' SIMILAR TO '%\mabc\M%'  true
'xabcy' SIMILAR TO '%\mabc\M%'  false
'abc' SIMILAR TO 'abc'          true
'abc' SIMILAR TO 'a'            false
'abc' SIMILAR TO '%(b|d)%'      true
'abc' SIMILAR TO '(b|c)%'       false
'-abc-' SIMILAR TO '%\mabc\M%'  true
'xabcy' SIMILAR TO '%\mabc\M%'  false
﻿
﻿

Metacharacter	Description
%	Repeated wildcard, matches any characters zero or more times. Equivalent to LIKE
_	Wildcard, matches exactly one of any character. Equivalent to LIKE
\|	alteration, either of two alternatives (a\|b represents a or b)
*	repetition zero or more times
+	repetition one or more times
?	repetition zero or one times
{m}	repetition exactly m times
{m,n}	repetition between m and n times (inclusive)
{m,}	repetition at least m times
()	Logical grouping
[]	A character class, equivalent to character classes in regular expressions. Example: [a-z] is any lower case English letter.
^	Beginning of line anchor as well as negation in character classes
$	End of line anchor

Metacharacter	Description
\a	The alert/bell character
\b	The backspace character
\B	A single \ character. Equivalent to \\
\cX	The character with the same 5 lower order bits as X with all zeros as higher order bits
\e	The character with numeric value 27, ESC
\f	Form feed
\n	Newline
\r	Carriage return
\t	Horizontal tab
\uwxyz	Where wxyz is 4 hexadecimal digits. Two characters, one with value numeric wx, and one with numeric value yz
\Uabcdefgh	Where abcdefg is 8 hexadecimal digits. 4 characters, numeric value ab, numeric value cd, numeric value ef, numeric value gh
\v	Vertical tab
\xd….	Where d is any number of hexadecimal digits. If the number of digits is odd, a 0 is added to the left of the sequence. numeric characters with values represented by every group of two symbols from d.
\0	Null character 0
\xy	Where xy are octal digits. The character with numeric value xy.
\xyz	Where xyz are octal digits. The character with numeric value xyz. If x >= 4, 2 characters are returned. Numeric value 1, and numeric value of the lower 8 bits of xyz.
\d	Matches any digit
\s	Matches any whitespace
\w	Matches any alphanumeric character or _
\D	Matches any non-digit
\S	Matches any non-whitespace
\W	Matches any non-alphanumeric that is also not _
\A	The beginning of a line, equivalent to ^
\m, \M, or \y	The boundary of a word. Equivalent to \b in normal regular expressions
\Y	Any position that is not the boundary of a word.
\Z	The end of a line. Equivalent to $ in normal regular expressions
\*	The star character
\+	The plus character
\?	The question mark character

Updated 21 Nov 2023

Did this page help you?

Scalar Data Conversion Functions

Sorted Aggregate Functions