The syntax for the LIKE search condition is as follows:
expression [ NOT ] LIKE pattern [ ESCAPE escape-character ]
expression The string to be searched.
pattern The pattern to search for within expression.
escape-character The character to use to escape special characters such as underscores and percent signs. The default escape character is the null character, which can be specified in a string literal as '\x00'.
The LIKE search condition attempts to match expression with pattern and evaluates to TRUE, FALSE, or UNKNOWN.
The search condition evaluates to TRUE if expression matches pattern (assuming NOT was not specified). If either expression or pattern is the NULL value, the search condition evaluates to UNKNOWN. The NOT keyword reverses the meaning of the search condition, but leaves UNKNOWN unchanged.
expression is interpreted as a CHAR or NCHAR string. The entire contents of expression is used for matching. Similarly, pattern is interpreted as a CHAR or NCHAR string and can contain any number of the supported wildcards from the following table:
| Wildcard | Matches |
|---|---|
| _ (underscore) | Any one character. For example, a_ matches ab and ac, but not a.
|
| % (percent) | Any string of zero or more characters. For example, bl% matches bl and bla.
|
| [] | Any single character in the specified range or set. For example, T[oi]m matches Tom or Tim.
|
| [^] | Any single character not in the specified range or set. For example, M[^c] matches Mb and Md, but not Mc.
|
All other characters must match exactly.
For example, the following search condition returns TRUE for any row where name starts with the letter a and has the letter b as its second last character.
... name LIKE 'a%b_' |
If escape-character is specified, it must evaluate to a single-byte CHAR or NCHAR character. The escape character can precede a percent, an underscore, a left square bracket, or another escape character in the pattern to prevent the special character from having its special meaning. When escaped in this manner, a percent matches a percent, and an underscore matches an underscore.
All patterns of 126 bytes or less are supported. Patterns of greater than 126 bytes that do not contains wildcards are not supported. Patterns containing wildcard characters that are longer than 126 bytes are supported, depending on the contents of the pattern. The number of bytes used to represent the pattern depends on whether the pattern is CHAR or NCHAR.
| To search for | Example | Additional information |
|---|---|---|
| One of a set of characters | LIKE 'sm[iy]th' |
A set of characters to look for is specified by listing the characters inside square brackets. In this example, the search condition matches smith and smyth. |
| One of a range of characters | LIKE '[a-r]ough' |
A range of characters to look for is specified by giving the ends of the range inside square brackets, separated by a hyphen. In this example, the search condition matches bough and rough, but not tough. The range of characters [a-z] is interpreted as "greater than or equal to a, and less than or equal to z", where the greater than and less than operations are carried out within the collation of the database. For information about matching ranges, see LIKE, REGEXP, and SIMILAR TO: differences in character comparisons. The lower end of the range must precede the higher end of the range. For example, |
| Ranges and sets combined | ... LIKE '[a-rt]ough' |
You can combine ranges and sets within square brackets. In this example, The pattern [a-rt] is interpreted as exactly one character that is either in the range a to r inclusive, or is t. |
| One character not in a range | ... LIKE '[^a-r]ough' |
The caret character (^) is used to specify a range of characters that is excluded from a search. In this example, The caret negates the rest of the contents of the brackets. For example, the bracket [^a-rt] is interpreted as exactly one character that is not in the range a to r inclusive, and is not t. |
| Search patterns with trailing blanks | '90 ', '90[ ]' and '90_' |
When your search pattern includes trailing blanks, the database server matches the pattern only to values that contain blanks—it does not blank pad strings. For example, the patterns '90 ', '90[ ]', and '90_' match the expression '90 ', but do not match the expression '90', even if the value being tested is in a CHAR or VARCHAR column that is three or more characters in width. |
Any single character in square brackets means that character. For example, [a] matches just the character a. [^] matches just the caret character, [%] matches just the percent character (the percent character does not act as a wildcard in this context), and [_] matches just the underscore character. Also, [[] matches just the character [.
Other special cases are as follows:
The pattern [a-] matches either of the characters a or -.
The pattern [] is never matched and always returns no rows.
The patterns [ or [abp-q return syntax errors because they are missing the closing bracket.
You cannot use wildcards inside square brackets. The pattern [a%b] finds one of a, %, or b.
You cannot use the caret character to negate ranges except as the first character in the bracket. The pattern [a^b] finds one of a, ^, or b.
If the database collation is case sensitive, the search condition is also case sensitive. To perform a case insensitive search with a case sensitive collation, you must include upper and lower characters. For example, the following search condition evaluates to true for the strings Bough, rough, and TOUGH:
LIKE '[a-zA-Z][oO][uU][gG][hH]' |
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 'Æ' LIKE 'AE' is false.
For a character-by-character comparison to match, each single character in the expression being searched must match a single character (using the collation's character equivalence), or a wildcard in the LIKE expression.
For a comparison of how matching and range evaluations are handled for LIKE, SIMILAR TO, and REGEXP, see LIKE, REGEXP, and SIMILAR TO search conditions.
LIKE 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. See Comparisons between CHAR and NCHAR.
You can specify expression or pattern as an NCHAR string literal by prefixing the quoted value with N (for example, expression LIKE N'pattern'). You can also use the CAST function to cast the pattern to CHAR or NCHAR (for example, expression LIKE CAST(pattern AS datatype).
See String literals, and CAST function [Data type conversion].
The semantics of a LIKE pattern does not change if the database is blank-padded since matching expression to pattern involves a character-by-character comparison in a left-to-right fashion. No additional blank padding is performed on the
value of either expression or pattern during the evaluation. Therefore, the expression a1 matches the pattern a1, but not the patterns 'a1 ' (a1, with a space after it) or a1_.
The LIKE search condition is a core feature of the ANSI SQL/2003 standard.
SQL Anywhere supports ANSI SQL/2003 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.
The use of character ranges and sets contained in square brackets [] is a vendor extension.
SQL Anywhere supports ANSI SQL/2003 feature T042, which permits LIKE search conditions to reference string-expressions that are LONG VARCHAR values.
LIKE search conditions that specify NCHAR string expressions or patterns is feature F421 of the ANSI SQL/2003 standard.
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |