User Guide

Query of Queries user guide 503
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)#';
Escaping wildcards
You can specify your own escape character using the conditional ESCAPE clause.
Example
The following example uses the ESCAPE clause to enable a search for a literal percent sign (%),
which ColdFusion normally interprets as a wildcard character:
SELECT emp_discount
FROM Benefits
WHERE emp_discount LIKE '10\%'
ESCAPE '\';