SyntaxThe syntax for the LIKE search condition is as follows:
expression [ NOT ] LIKE pattern
Parametersexpression The string to be searched.
pattern The pattern to search for within expression.
RemarksThe 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 and pattern are interpreted as CHAR strings. pattern 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.
|
ExampleThe following search condition returns TRUE for any row where column-name starts with the letter a and has the letter b as its second last character:
SELECT * FROM table-name WHERE column-name LIKE 'a%b_' |
Different ways to use the LIKE search condition| 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. 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. |
Special cases of ranges and setsAny 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 [.
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.
Case sensitivity and how comparisons are performedIf 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]' |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |