Left and right square brackets are used to specify a character class. A character class is a set of characters to match against.

With the exception of the hyphen (-) and the caret (^), metacharacters and quantifiers (such as * and {m}, respectively) specified within a character class have no special meaning and are evaluated as actual characters.

SQL Anywhere also supports sub-character classes such as POSIX character classes. See Regular expressions: Special sub-character classes.

A hyphen can be used within a character class to denote a range. For example, REGEXP '[a-e]' matches a, b, c, d, and e.

For details on how ranges are evaluated by REGEXP and SIMILAR TO, see LIKE, REGEXP, and SIMILAR TO: differences in character comparisons.

The percent sign can be used with SIMILAR TO to match any number of characters.

The percent sign is not considered a metacharacter for REGEXP and REGEXP_SUBSTR. When specified, it matches a percent sign (%).

The underscore can be used with SIMILAR TO to match a single character.

The underscore is not considered a metacharacter for REGEXP and REGEXP_SUBSTR. When specified, it matches an underscore (_).

The pipe symbol is used to specify alternative patterns to use for matching the string. In a string of patterns separated by a vertical bar, the vertical bar is interpreted as an OR and matching stops at the first match made starting from the leftmost pattern. So, you should list the patterns in descending order of preference. You can specify an unlimited number of alternative patterns.

Left and right curly braces are metacharacters when used for specifying quantifiers. Quantifiers specify the number of times a pattern must repeat to constitute a match. For example:

For REGEXP and REGEXP_SUBSTR, when a caret is outside a character class, the caret matches the start of a string. For example, '^[hc]at' matches hat and cat, but only at the beginning of the string.

When used inside a character class, the following behavior applies:

When used with REGEXP and REGEXP_SUBSTR, matches the end of a string. For example, SIMILAR TO 'cat$' matches cat, but not catfish.

When used with SIMILAR TO, it matches a question mark.

When used with REGEXP and REGEXP_SUBSTR, matches any single character. For example, REGEXP 'a.cd' matches any string of four characters that starts with a and ends with cd.

When used with SIMILAR TO, matches a period (.).

The colon is used within a character set to specify a subcharacter class. For example, '[[:alnum:]]'.

Matches upper- and lowercase alphabetic characters in the current collation. For example, '[0-9]{3}[[:alpha:]]{2}' matches three digits, followed by two letters.

Match digits, and upper- and lowercase alphabetic characters in the current collation. For example, '[[:alnum:]]+' matches a string of one or more letters and numbers.

Match digits in the current collation. For example, '[[:digit:]-]+' matches a string of one or more digits or dashes. Likewise, '[^[:digit:]-]+' matches a string of one or more characters that are not digits or dashes.

Match lowercase alphabetic characters in the current collation. For example, '[[:lower:]]' does not match A because A is uppercase.

Match a single blank (' '). For example, the following statement searches Contacts.City for any city with a two word name:

SELECT City 
FROM Contacts
WHERE City REGEXP '.*[[:space:]].*';

Match uppercase alphabetic characters in the current collation. For example, '[[:upper:]ab]' matches one of: any upper case letter, a, or b.

Match a whitespace character such as space, tab, formfeed, and carriage return.

Match any seven-bit ASCII character (ordinal value between 0 and 127).

Match a blank space, or a horizontal tab.

[[:blank:]] is equivalent to [ \t].

Match ASCII characters with an ordinal value of less than 32, or character value 127 (control characters). Control characters include newline, form feed, backspace, and so on.

Match printed characters.

[[:graph:]] is equivalent to [[:alnum:][:punct:]].

Match printed characters and spaces.

[[:print:]] is equivalent to [[:graph:][:whitespace:]].

Match one of: !"#$%&'()*+,-./:;<=>?@[\]^_`{|}~.

The [:punct:] sub-character class may not include non-ASCII punctuation characters available in the current collation.

Match alphabetic, digit, or underscore characters in the current collation.

[[:word:]] is equivalent to [[:alnum:]_].

Match a character that is in the character class [0-9A-Fa-f].

Matches the character whose value is \0xxx, where xxx is any sequence of octal digits, and 0 is a zero. For example, \0134 matches a backslash.

Matches the bell character.

Used outside a character set to match the start of a string.

Equivalent to ^ used outside a character set.

Matches a backspace character.

Matches the backslash character (\).

Matches a named control character. For example, \cZ for ctrl-Z.

Matches a digit in the current collation. For example, the following statement searches Contacts.Phone for all phone numbers that end with 00:

SELECT Surname, Surname, City, Phone
   FROM Contacts
   WHERE Phone REGEXP '\\d{8}00';

\d can be used both inside and outside character classes, and is equivalent to [[:digit:]].

Matches anything that is not a digit. This is the opposite of \d.

\D can be used both inside and outside character classes, and is equivalent to [^[:digit:]].

Be careful when using the negated shorthands inside square brackets. [\D\S] is not the same as [^\d\s]. The latter matches any character that is not a digit or whitespace. So it matches x, but not 8. The former, however, matches any character that is either not a digit, or is not whitespace. Because a digit is not whitespace, and whitespace is not a digit, [\D\S] matches any character, digit, whitespace or otherwise.

Matches the escape character.

Ends the treatment of metacharacters as non-metacharacters, initiated by a \Q.

For a list of regular expression metacharacters, see Regular expressions: Metacharacters.

Matches a form feed.

Matches a new line.

Treat all metacharacters as non-metacharacters, until \E is encountered. For example, \Q[$\E is equivalent to \[\$.

For a list of regular expression metacharacters, see Regular expressions: Metacharacters.

Matches a carriage return.

Matches a space or a character treated as whitespace. For example, the following statement returns all product names from Products.ProductName that have at least one space in the name:

SELECT Name 
FROM Products
WHERE Name REGEXP '.*\\s.*'

\s can be used both inside and outside character classes, and is equivalent to [[:whitespace:]]. See Regular expressions: Special sub-character classes.

Matches a non-whitespace character. This is the opposite of \s, and is equivalent to [^[:whitespace:]].

\S can be used both inside and outside character classes. See Regular expressions: Special sub-character classes.

Be careful when using the negated shorthands inside square brackets. [\D\S] is not the same as [^\d\s]. The latter matches any character that is not a digit or whitespace. So it matches x, but not 8. The former, however, matches any character that is either not a digit, or is not whitespace. Because a digit is not whitespace, and whitespace is not a digit, [\D\S] matches any character, digit, whitespace or otherwise.

Matches a horizontal tab.

Matches a vertical tab.

Matches a alphabetic character, digit, or underscore in the current collation. For example, the following statement returns all surnames from Contacts.Surname that are exactly seven alpha-numeric characters in length:

SELECT Surname 
FROM Contacts 
WHERE Surname REGEXP '\\w{7}';

\w can be used both inside and outside character classes. See Regular expressions: Special sub-character classes.

Equivalent to [[:alnum:]_]..

Matches anything that is not an alphabetic character, digit, or underscore in the current collation. This is the opposite of \w, and is equivalent to [^[:alnum:]_].

This regular expression can be used both inside and outside character classes. See Regular expressions: Special sub-character classes.

Matches the character whose value is 0xhh, where hh is, at most, two hex digits. For example, \x2D is equivalent to a hyphen.

Equivalent to \x{hh}.

Matches the character whose value is 0xhhh, where hhh is, at most, eight hex digits.

Matches the position (not character) at the end of the string.

Equivalent to $.

Positive lookahead zero-width assertion   Looks to see if the current position in the string is immediately followed by an occurrence of pattern, without pattern becoming part of the match string. 'A(?=B)' matches an A that is followed by a B, without making the B part of the match.

For example, SELECT REGEXP_SUBSTR( 'in new york city', 'new(?=\\syork)'); returns the substring new since it is immediately followed by ' york' (note the space before york).

Negative lookahead zero-width assertions   Looks to see if the current position in the string is not immediately followed by an occurrence of pattern, without pattern becoming part of the match string. So, 'A(?!B)' matches an A that is not followed by a B.

For example,SELECT REGEXP_SUBSTR('new jersey', 'new(?!\\syork)'); returns the substring new.

Positive lookbehind zero-width assertions   Looks to see if the current position in the string is immediately preceded by an occurrence of pattern, without pattern becoming part of the match string. So, '(?<=A)B' matches a B that is immediately preceded by an A, without making A part of the match.

For example, SELECT REGEXP_SUBSTR('new york', '(?<=new\\s)york'); returns the substring york.

Negative lookbehind zero-width assertions   Looks to see if the current position in the string is not immediately preceded by an occurrence of pattern, without pattern becoming part of the match string.

For example, SELECT REGEXP_SUBSTR('about york', '(?<!new\\s)york'); returns the substring york.

Possessive local subexpression   Matches only the largest prefix of the remaining string that matches pattern.

For example, in 'aa' REGEXP '(?>a*)a', (?>a*) matches (and consumes) the aa, and never just the leading a. As a result, 'aa' REGEXP '(?>a*)a' evaluates to false.

Non-capturing block   This is functionally equivalent to just pattern, and is provided for compatibility.

For example, in 'bb' REGEXP '(?:b*)b', (?:b*) matches (and consumes) the bb. However, unlike possessive local subexpression, the last b in bb is given up to allow the whole match to succeed (that is, to allow the matching to the b found outside the non-capturing block).

Likewise, 'a(?:bc|b)c' matches abcc, and abc. In matching abc, backtracking on the final c in bc takes place so that the c outside the group can be used to make the match successful.

Used for comments. The content of text is ignored.