440
DEVELOPING
COLDFUSION 9 APPLICATIONS
Accessing and Using Data
Las
t
upda
te
d 8/5/2010
Syntax
like_cond ::= left_string_exp [NOT] LIKE right_string_exp [ESCAPE escape_char]
The left_string_exp can be either a constant string, or a column reference to a string column. The right_string_exp can
be either a column reference to a string column, or a search pattern. A
search pattern
is a search condition that consists
of literal text and at least one wildcard character. A
wildcard character
is a special character that represents an unknown
part of a search pattern, and is interpreted as follows:
•
The underscore (_) represents any single character.
•
The percent sign (%) represents zero or more characters.
•
Brackets [ ] represents any character in the range.
•
Brackets with a caret [^] represent any character not in the range.
•
All other characters represent themselves.
Note:
Earlier versions of ColdFusion do not support bracketed ranges.
Examples
The following example uses the LIKE conditional to retrieve only those dogs of the breed Terrier, whether the dog is
a Boston Terrier, Jack Russell Terrier, Scottish Terrier, and so on:
SELECT dog_name, dog_IQ, breed
FROM Dogs
WHERE breed LIKE '%Terrier';
The following examples are select statements that use bracketed ranges:
SELECT lname FROM Suspects WHERE lname LIKE 'A[^c]%';
SELECT lname FROM Suspects WHERE lname LIKE '[a-m]%';
SELECT lname FROM Suspects WHERE lname LIKE '%[]';
SELECT lname FROM Suspects WHERE lname LIKE 'A[%]%';
SELECT lname FROM Suspects WHERE lname LIKE 'A[^c-f]%';
Case sensitivity
Unlike the rest of ColdFusion, Query of Queries is case-sensitive. However, Query of Queries supports two string
functions,
UPPER()
and
LOWER()
, which you can use to achieve case-insensitive matching.
Examples
The following example matches only 'Sylvester':
SELECT dog_name
FROM Dogs
WHERE dog_name LIKE 'Sylvester';
The following example is not case sensitive; it uses the
LOWER()
function to treat 'Sylvester', 'sylvester', 'SYLVESTER',
and so on, as all lowercase, and matches them with the all lowercase string, ‘sylvester’:
SELECT dog_name
FROM Dogs
WHERE LOWER(dog_name) LIKE 'sylvester';
If you use a variable on the right side of the LIKE conditional and want to ensure that the comparison is not case-
sensitive, use the
LCase
or
UCase
function to force the variable text to be all of one case, as in the following example:
WHERE LOWER(dog_name) LIKE '#LCase(FORM.SearchString)#';