The REGEXP search condition matches a whole string, not a substring. To match on a substring with the string, enclose the
string in wildcards that match the rest of the string (.*pattern.*). For example, SELECT ... WHERE Description REGEXP 'car' matches only car, not sportscar. However, SELECT ... WHERE Description REGEXP '.*car' matches car, sportscar, and any string that ends with car. Alternatively, you can rewrite your query to make use the REGEXP_SUBSTR
function, which is designed to search for substrings within a string.
When matching against only a sub-character class, you must include the outer square brackets and the square brackets for the
sub-character class. For example, expression REGEXP '[[:digit:]]'.
REGEXP only matches a literal in a pattern if it is the exact same character (that is, they have the same code point value).
Ranges in character classes (for example, '[A-F]') only match characters that code point values greater than or equal to the code point value of the first character in the
range (A) and less than or equal to the code point value of the second character in the range (F).
Comparisons are performed character-by-character, unlike the equivalence (=) operator and other operators where the comparison
is done string-by-string. For example, when a comparison is done in a UCA collation (CHAR or NCHAR with the collation set
to UCA), 'Æ'='AE' is true, but 'Æ' REGEXP 'AE' is false.
REGEXP search conditions can be used to compare CHAR and NCHAR strings. In this case, character set conversion is performed
so that the comparison is done using a common data type. Then, a code point by code point comparison is performed.
You can specify expression or pattern as an NCHAR string literal by prefixing the quoted value with N (for example, expression REGEXP N'pattern'). You can also use the CAST function to cast the pattern to CHAR or NCHAR (for example, expression REGEXP CAST(pattern AS datatype).
SQL/2008
The REGEXP search condition is a vendor extension, but is roughly compatible with the LIKE_REGEX search condition
of the SQL/2008 standard, which is SQL language feature F841.
SQL Anywhere supports ANSI SQL/2008 feature F281, which permits the pattern and escape-expressions to be arbitrary expressions
evaluated at execution time. Feature F281 also permits expression to be an expression more complex than a simple column reference.
SQL Anywhere supports ANSI SQL/2008 feature T042, which permits REGEXP search conditions to reference string-expressions that
are LONG VARCHAR values.
REGEXP search conditions that specify NCHAR string expressions or patterns is feature F421 of the ANSI SQL/2008 standard.