Regular expressions

A regular expression is a sequence of characters, wildcards, or operators that defines a pattern to search for within a string. SQL Anywhere supports regular expressions as part of a REGEXP or SIMILAR TO search conditions in the WHERE clause of a SELECT statement, or as an argument to the REGEXP_SUBSTR function. The LIKE search condition does not support regular expressions, although some of the wildcards and operators you can specify with LIKE resemble the regular expression wildcards and operators.

The following SELECT statement uses a regular expression ((K|C[^h])%) to search the Contacts table for contacts whose last name begins with K or C, but not Ch:

SELECT Surname, GivenName 
   FROM Contacts
   WHERE Surname REGEXP '(K|C[^h])%';

Aside from being a pattern that represents a string you are searching for, a regular expression can include additional syntax to specify grouping, quantification, assertions, and alternation, as described below.

Grouping Grouping allows you to group parts of a regular expression to apply some additional matching criteria. For example, '(abc){2}' matches abcabc.
You can also use grouping to control the order in which the parts of the expression are evaluated. For example, 'ab(cdcd)' looks first for an incidence of cdcd, and then evaluates whether the instance of cdcd is preceded by ab.
Quantification Quantification allows you to control the number of times the preceding part of the expression can occur. For example, a question mark (?) is a quantifier that matches zero or one instance of the previous character. Consequently, 'honou?r' matches both honor and honour.
Assertions Normally, searching for a pattern returns that pattern. Assertions allow you to test for the presence of a pattern, without having that pattern become part of what is returned. For example, 'SQL(?= Anywhere)' matches SQL only if it is followed by a space and then Anywhere.
Alternation Alternation allows you to specify alternative patterns to search for if the preceding pattern cannot be found. Alternate patterns are evaluated from left to right, and searching stops at the first match. For example, 'col(o|ou)r' looks for an instance of color. If no instance is found, colour is searched for instead.

Regular expressions

See also