MACROMEDIA COLDFUSION 4.5-DEVELOPING WEB, Руководство по разработке

Страница: 65 / 380

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>