SIMILAR TO Operator
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.
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 |
- 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 '\\\\'.