ASA SQL Reference
SQL Language Elements
Search conditions
The syntax for LIKE conditions is as follows:
expr [NOT] LIKE pattern [ESCAPE escape-expr]
The LIKE condition can evaluate as TRUE, FALSE, or UNKNOWN.
Without the NOT keyword, the condition evaluates as TRUE if expression matches the pattern. If either expression or pattern is the NULL value, this condition is UNKNOWN. The NOT keyword reverses the meaning of the condition, but leaves UNKNOWN unchanged.
The pattern may contain any number of wildcards. The wildcards are:
| Wildcard | Matches |
|---|---|
| _ (underscore) | Any one character |
| % (percent) | Any string of zero or more characters |
| [] | Any single character in the specified range or set |
| [^] | Any single character not in the specified range or set |
All other characters must match exactly.
For example, the search condition
... name LIKE 'a%b_'
is TRUE for any row where name starts with the letter a and has the letter b as its second last character.
If an escape-expr is specified, it must evaluate to a single character. The 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 will match a percent, and an underscore will match an underscore.
All patterns of length 126 characters or less are supported. Patterns of length greater than 254 characters are not supported. Some patterns of length between 127 and 254 characters are supported, depending on the contents of the pattern.
A set of characters to look for is specified by listing the characters inside square brackets. For example, the following condition finds the strings smith and smyth :
LIKE 'sm[iy]th'
A range of characters to look for is specified by giving the ends of the range inside square brackets, separated by a hyphen. For example, the following condition finds the strings bough and rough , but not tough :
LIKE '[a-r]ough'
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 on 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, a LIKE condition containing the expression [z-a] returns no rows because no character matches the [z-a] range.
Unless the database is created as case sensitive, the range of characters is case insensitive. For example, the following condition finds the strings Bough , rough , and TOUGH :
LIKE '[a-z]ough'
If the database is created as a case-sensitive database, the search condition is case sensitive also. To perform a case insensitive search in a case sensitive database, you must include upper and lower characters. For example, the following condition finds the strings Bough , rough , and TOUGH :
LIKE '[a-zA-Z][oO][uU][gG][hH]'
You can combine ranges and sets within a square bracket. For example, the following condition finds the strings bough , rough , and tough :
... LIKE '[a-rt]ough'
The bracket [a-mpqs-z] is interpreted as "exactly one character that is either in the range a to m inclusive, or is p, or is q, or is in the range s to z inclusive".
The caret character (^) is used to specify a range of characters that is excluded from a search. For example, the following condition finds the string tough , but not the strings rough , or bough :
... LIKE '[^a-r]ough'
The caret negates the entire rest of the contents of the brackets. For example, the bracket [^a-mpqs-z] is interpreted as "exactly one character that is not in the range a to m inclusive, is not p , is not q , and is not in the range s to z inclusive".
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 expression [a-] matches either of the characters a or - .
The expression [] is never matched and always returns no rows.
The expressions [ or [abp-q are ill-formed expressions, and give syntax errors.
You cannot use wildcards inside square brackets. The expression [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 expression [a^b] finds one of a , ^ , or b.
When your search pattern includes trailing blanks, Adaptive Server Anywhere matches the pattern only to values that contain blanks—it does not blank-pad strings. For example, the search patterns '90 ', '90[ ]' and '90_' match the value '90 ', but do not match the value '90', even if the value being tested is in a char or varchar column that is three or more characters in width.
The ESCAPE clause is supported by Adaptive Server Anywhere only.