The LIKE Operator in SQL

Imagine another scenario. You have a Web form that allows your customers to search through an inventory table to determine whether you carry the product in which they are interested. After you gather the desired product name from the customer, you use a simple SELECT query to return all matches, as follows:

SELECT * FROM CATALOG WHERE TITLE='" & Request.Form("Title") & " ' "

In this example, the only records returned are those where the Title field has an exact value specified by the customer. This is potentially useful, assuming that the customer knows the exact title of the CD he or she is interested in buying.

Often, customers do not know exact information, or they have only partial information. In this case, the preceding query would not work because it is looking for an exact match to return any data.

You need to use the LIKE operator, which allows your queries to generate results where the information provided is like what is found in the database table. Take a look at the following example:

SELECT * FROM CATALOG WHERE TITLE LIKE '%Live%'

By specifying the LIKE operator, using the syntax of %Live%, all records containing the word Live in the Title field are returned - it does not matter in what location the word Live appears. For example, if the title of a CD was Absolutely Live, this would be returned as well.

How you utilize the % or _ characters in the LIKE operator determines how this operator performs in a query. In the following query, all records where the Title field has the letters RO in the second and third position are returned:

SELECT * FROM CATALOG WHERE TITLE LIKE '_RO'

In the next example, all records that begin with the letter P are returned:

SELECT * FROM CATALOG WHERE TITLE LIKE 'P_%'

The next query returns all records in the Catalog table that have, in the Title field, a value that ends with the letter P:

SELECT * FROM CATALOG WHERE TITLE LIKE '%P'

The following query returns all records in the Catalog table with a value in the Title field that has the letter O in the second position and ends with the letter R:

SELECT * FROM CATALOG WHERE TITLE LIKE '%_O%R'

As you see from these examples, there is virtually no limit to the way you can use the LIKE operator to help locate data based on partial information. As you develop more advanced SQL queries in your ASP Web pages, you will return to the LIKE operator frequently.