MACROMEDIA COLDFUSION 5-DEVELOPING, Develop Manual

Page: 71 / 438

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:
<cfquery name="GetEmployees" datasource="CompanyInfo">
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:
<cfquery name="GetDepartments" datasource="CompanyInfo">
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:
<cfquery name="GetEmployees" datasource="CompanyInfo">
SELECT FirstName, LastName,
StartDate, Salary, Contract
FROM Employee
WHERE Contract = ’Yes’
AND Salary > 50000
</cfquery>