Developing Web Applications with ColdFusion
|
|
Chapter 4 : Retrieving and Formatting the Data You Want
|
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.
|
Copyright © 1999, Allaire Corporation. All rights reserved.
|
|