The syntax for the LIKE search condition is as follows:
expression [ NOT ] LIKE pattern
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 and pattern are interpreted as CHAR strings. pattern can contain any number of the supported wildcards from the following table:
|_ (underscore)||Any one character. For example,
|% (percent)||Any string of zero or more characters. For example,
|||Any single character in the specified range or set. For example,
|[^]||Any single character not in the specified range or set. For example,
The 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_'
|To search for||Example||Additional information|
|One of a set of characters||
||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||
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||
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||
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||
||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 [.
[a-] matches either of the characters a or -.
 is never matched and always returns no rows.
[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:
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|