Match a pattern against a string.
Syntaxexpression [ NOT ] SIMILAR TO pattern [ ESCAPE escape-expression ]
Parametersexpression The expression to be searched.
pattern The regular expression to search for within expression.
escape-expression The escape character to use in the match. The default escape character is the null character, which can be specified in a string literal as '\x00'.
| Regular expression syntax | Meaning |
|---|---|
| \ x | Match anything that compares equal to x, where the escape character is assumed to be the backslash character (\). For example, \[ matches '['.
|
| x | Any character (other than a meta-character) matches itself. For example, A matches 'A'.
|
RemarksTo match a substring with the string, use the percentage sign wildcard (%expression). For example, SELECT ... WHERE Description SIMILAR TO 'car' matches only car, not sportscar. However, SELECT ... WHERE Description SIMILAR TO '%car' matches car, sportscar, and any string that ends with car.
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 SIMILAR TO '[[:digit:]]').
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 'Æ' SIMILAR TO 'AE' is false.
For a character-by-character comparison to match, each single character in the expression being searched must match a single character or a wildcard in the SIMILAR TO pattern.
Database collation and matchingSIMILAR TO use the collation to determine character equivalence and evaluate character class ranges. For example, if the database is case- and accent-insensitive, matches are case- and accent-insensitive. Ranges are also evaluated using the collation sort order.
National character (NCHAR) supportSIMILAR TO 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 character-by-character comparison is performed.
You can specify expression or pattern as an NCHAR string literal by prefixing the quoted value with N (for example, expression SIMILAR TO N'pattern'). You can also use the CAST function to cast the pattern to CHAR or NCHAR (for example, expression SIMILAR TO CAST(pattern AS datatype).
See also Standards and compatibilitySQL/2008 The SIMILAR TO predicate is optional SQL language feature T141 of the SQL/2008 standard.
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |