Chapter 4: Retrieving and Formatting the Data You Want
39
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 is known as pattern matching and
often used to query databases.
For example, to return data for employees whose last name starts with AL and ends
with anything, you would build a query that looks like this:
<CFQUERY NAME="GetEmployees" DATASOURCE="CompanyInfo">
SELECT FirstName, LastName,
StartDate, Salary, Contract
FROM Employees
WHERE LastName LIKE ’AL%’
</CFQUERY>
•
The LIKE operator tells the database that the string that follows should be used
for pattern matching.
•
The LIKE operator tells the database that the string that follows should be used
for pattern matching.
•
If you placed a wildcard before and after AL, you would retrieve any record in
that column that contains AL.
•
Surround strings in SQL statements with single quotes (’).
•
When comparing a value against a numeric field, don’t surround the value with
single quotes (’).
Note
By default, SQL is not case-sensitive.
Filtering Data Based on Multiple Conditions
Combine a SQL WHERE clause with a SQL AND clause in your queries when you want
to retrieve data based on the results of more than one comparison.
For example, to return data for contract employees who earn more than $50,000, you
would build a query that looks like this:
<CFQUERY NAME="GetEmployees" DATASOURCE="CompanyInfo">
SELECT FirstName, LastName
StartDate, Salary, Contract
FROM Employees
WHERE Contract = ’Yes’
AND Salary > 50000
</CFQUERY>
Summary of Contents for COLDFUSION 4.5-DEVELOPING WEB
Page 1: ...Allaire Corporation Developing Web Applications with ColdFusion ColdFusion 4 5...
Page 14: ...xiv Developing Web Applications with ColdFusion...
Page 26: ...xxvi Developing Web Applications with ColdFusion...
Page 34: ...8 Developing Web Applications with ColdFusion...
Page 70: ...44 Developing Web Applications with ColdFusion...
Page 84: ...58 Developing Web Applications with ColdFusion...
Page 114: ...88 Developing Web Applications with ColdFusion...
Page 148: ...122 Developing Web Applications with ColdFusion...
Page 174: ...148 Developing Web Applications with ColdFusion...
Page 208: ...182 Developing Web Applications with ColdFusion...
Page 244: ...218 Developing Web Applications with ColdFusion...
Page 274: ...248 Developing Web Applications with ColdFusion...
Page 288: ...262 Developing Web Applications with ColdFusion...
Page 300: ...274 Developing Web Applications with ColdFusion...
Page 350: ...324 Developing Web Applications with ColdFusion...
Page 362: ...336 Developing Web Applications with ColdFusion...
