User Guide

Working with Queries and Data 51

Performing pattern matching

Use the SQL LIKE operator and SQL wildcard strings in a SQL WHERE clause when

you want to compare a value against a character string field so that the query returns

database information based on commonalities. This technique is known as pattern

matching and is often used to query databases.

For example, to return data for employees whose last name starts with AL, you build

a query that looks like this:

SELECT FirstName, LastName,

StartDate, Salary, Contract

FROM Employee

WHERE LastName LIKE ’AL%’

</cfquery>

• The LIKE operator tells the database to use the string that follows for pattern

matching.

• If you place a wildcard before and after AL, you retrieve any record in that

column that contains AL.

• Surround strings in SQL statements with single quotes (

’).

To return information from the Departmt table on all departments except the sales

department, you would build a query that looks like this:

SELECT *

FROM Departmt

WHERE Dept_Name NOT LIKE ’[Ss]ales’

</cfquery>

• The first character in the match can be either upper case S or lower case s.

Note

Whether SQL identifiers and data comparison operations are case sensitive depends

on the database.

Filtering data based on multiple conditions

When you want to retrieve data based on the results of more than one comparison

you can use AND and OR operators to combine conditions.

For example, to return data for contract employees who earn more than $50,000,

would build a query that looks like this:

SELECT FirstName, LastName,

StartDate, Salary, Contract

FROM Employee

WHERE Contract = ’Yes’

AND Salary > 50000

</cfquery>