The syntax for the LIKE search condition is as follows:
expression [ NOT ] LIKE pattern [ ESCAPE escape-expression ]
expression The string to be searched.
pattern The pattern to search for within expression.
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 an escape-expression 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 ordering of characters within a collation, see International languages and character sets. 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. |
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]' |
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:
[a-] matches either of the characters a or -.
[] is never matched and always returns no rows.
[ or [abp-q return syntax errors because they are missing the closing bracket.
[a%b] finds one of a, %, or b.
[a^b] finds one of a, ^, or b.
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.
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 (or code point by code point, in the case of UTF8 databases) 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 (or code point by code point, in the case of UTF8 databases) 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_.
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |