A Simple Example Using LIKE

I need information on a book in the titles table. The only problem is, I can’t remember the exact name of the book. I remember that the title has the word “Computer” in it, but that’s all I remember. If I had a table with just a few rows in it, it would be easy to select all of them and eyeball the list for the book I want. But if my titles table actually contained thousands of books, I would want to narrow the list before going over it. Here is the query I would use to return all titles containing the word “Computer”:

select    title_id, title
from      titles
where     title like “%Computer%”

title
--------------------------------------
Computer Phobic AND Non-Phobic Individuals: Behavior Variations
Cooking with Computers: Surreptitious Balance Sheets
Straight Talk About Computers
The Psychology of Computer Cooking
You Can Combat Computer Stress!

(5 row(s) affected)

This query would also match the (imaginary) title, The Computer Cookbook because it contains the word “Computer” with some characters before and some characters after. It would also match The Big, Red Computer or Computer Programming. It wouldn’t match Does Not Compute, because this title doesn’t contain the word “Computer.” It also would not match an uncapitalized Uncomputerlike Behavior (see the warning following this analysis regarding case sensitivity).

---

Warning:  Depending on the sort order installed on your server, searches may or may not be case sensitive. The binary sort order, which is fastest and thus a popular choice, is case sensitive. In a case sensitive search, you must account for mixed case possibilities if you want to match both capital and lowercase letters. I’ll cover how to do that with LIKE in just a moment.

---

If I had a slightly better memory, I might have remembered that the title started with “Computer.” If I had, I could have gotten the book right away by eliminating the first percent sign (%). This query will match any title that starts with “Computer”:

select    title
from      titles
where     title like “Computer%”

title
--------------------------------------
Computer Phobic AND Non-Phobic Individuals: Behavior Variations

(1 row(s) affected)

---

Technical Note:  When using LIKE, if you begin the string with a wildcard character, the server cannot use indexes to search for matching values. This will cause performance problems on large tables. This is because you are asking for “some string that starts out any old way and has some stuff I care about in the middle.” If, however, you begin a LIKE with a constant string and use wildcards at the end, or in the middle, the server can use an index to find strings that might match and then evaluate those strings to make a final determination about whether or not to return them.

If the server can use an index to satisfy your query, you’ll get results much faster.

---

Four wildcard characters are available for you to use with the LIKE operator. The characters, and their usage, are summarized in Table 3.5.

How to Match Uppercase and Lowercase Letters

A string containing any one of the characters in brackets will match the row. So, for example, if I asked for SELECT au_id from authors WHERE lname LIKE “Mc[Ee]wan”, I would match both McEwan and Mcewan. (I wouldn’t match McEewan since the brackets match just one character.

This is helpful when you aren’t sure if a value will be capitalized or if there are many values, some capitalized, some not. This happens sometimes when data comes from different sources into your central location.

---

Warning:  The wildcard characters are only valid when used in concert with the LIKE keyword. A mistake I see made over and over again (and have made a few times, myself) is to attempt to use the % wildcard in a WHERE clause without the LIKE. This query would only match a string that actually contained a percent sign after the word “Computer”:

select    title
from      titles
where     title = “Computer%”

---

Using LIKE to Find Dates

LIKE can also be used to search dates. When LIKE is used to search for dates, indexes cannot be used, and thus the performance is poor. However, in some cases LIKE is the only way to find certain information.

The server will convert each date column to a character so that it can be compared with the string in the LIKE.

An Example of Using LIKE to Search Dates

Suppose I have a sales table like the one used earlier today. That table had three columns: invoice_num, saledate, and price. I’ve been in business for a couple of years, and I’ve been adding to my sales table the entire time, so it has a lot of rows in it.

I have a theory that people spend more money in the morning than they do in the afternoon. To verify this, I would like to see the sales amounts for purchases made in the 9:00 a.m. hour versus the purchases made during the 5:00 p.m. hour.

select  *
from    invoices
where   saledate like “9:__ AM”

select  *
from    invoices
where   saledate like “5:__ PM”