Chapter 6 SQL Language Elements
243
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
wild card 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 wild cards 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.
♦ The ESCAPE clause is supported by Adaptive Server Anywhere only.
IN conditions
The syntax for IN conditions is as follows:
...
expression
[ NOT ] IN (
subquery
)
|
expression
[ NOT ] IN (
expression
)
|
expression
[ NOT ] IN (
value-expr1
,
value-expr2
[,
value-
expr3
] ... )
Without the NOT keyword, the IN conditions is TRUE if expression equals
any of the listed values, UNKNOWN if expression is the NULL value, and
FALSE otherwise. The arguments value-expr1, value-expr2, and value-expr3
are expressions that take on a single value, which may be a string, a number,
a date, or other SQL data type. The NOT keyword reverses the meaning of
the condition, but leaves UNKNOWN unchanged.
Searching for one
character not in a
range
Special cases of
ranges and sets
Compatibility
To Next Page
Loading...