Using LIKE and NOT LIKE

Using numbers, dates, and NULLs in conditionals is a straightforward process, but strings can be trickier. You can check for string equality with a query such as

SELECT * FROM users WHERE user_name = 'trout'

However, comparing strings in a more liberal manner requires extra operators and characters. If, for example, you wanted to match a person's last name that could be Smith or Smiths or Smithson, you would need a more flexible query. This is where the LIKE and NOT LIKE conditionals come in. These are usedprimarily with stringsin conjunction with two wildcard characters: the underscore (_), which matches a single character, and the percentage sign (%), which matches zero or more characters. In my last name example, the query I would write would be

SELECT * FROM users WHERE last_name LIKE 'Smith%'

This query will perform a search on all columns whose last_name value begins with Smith. Because it's a case-insensitive search by default, it would also apply to names that begin with smith.

The \G Terminator in mysql As I alluded to earlier in this chapter (see the sidebar "Semicolons and Queries"), you can terminate your queries in the mysql client using \G instead of a semicolon. The benefit of this is that your results will be displayed as a vertical list of records instead of a horizontal one. When selecting many columns or an entire table, this is often a more legible way to view the results. For this reason, I'll occasionally use \G throughout the book. Remember that this is just a way to change the look of the output and also means that no semicolon is required. Also, this is a mysql client feature, not an aspect of SQL.

To use LIKE:

1.	Select all of the client information in which the client's contact has a last name of Doe (Figure 5.11). SELECT * FROM clients WHERE contact_last_name='Doe'\G Figure 5.11. Simple, case-sensitive equality tests can be accomplished using the equality operator. The \G at the end of the query tells mysql to show the results as a vertical list of records. [View full size image] If you're hoping to match a literal string, do not use LIKE, use the equality comparison operator instead. If you're paying attention, you're probably wondering what that \G is doing at the end of the query. This is for aesthetic purposes and is explained in the sidebar.
2.	Select all of the client information in which the client's contact has a last name of Doe, case-insensitive (Figure 5.12). SELECT * FROM clients WHERE last_name LIKE 'Doe'\G Figure 5.12. Using LIKE allows me to perform a case-insensitive search on a text column. [View full size image] Because LIKE is case-insensitive, I can use it to find a literal string regardless of capitalization. No wildcard character is needed in this situation.
3.	Select the client and contact names for every record whose contact's first name is not John, Joe, Joey, etc. (Figure 5.13).

[View full width]
SELECT client_name, contact_first_name, contact_last_name FROM clients WHERE
 contact_first_name NOT LIKE 'Jo%';

Figure 5.13. The NOT LIKE operator allows you to eliminate certain records based upon loose definitions (such as Jo%).

[View full size image]

If I want to rule out certain possibilities, I can use NOT LIKE with the wildcard.