| 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. 
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. 
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%). 
If I want to rule out certain possibilities, I can use NOT LIKE with the wildcard.  Tips
The wildcard characters can be used at the front and/or the back of a string in your queries. SELECT * FROM users WHERE user_name = '_smith%' Queries with a LIKE conditional are generally slow, so use this format sparingly. Conditionals such as LIKE 'some%' can still make use of indexes, which improves their speed, but conditionals with an initial wildcard (LIKE '%some%' or LIKE '%some') cannot, making them much less efficient. Although LIKE and NOT LIKE are normally used with strings, they can also be applied to numeric columns, should the need arise. That being said, there are normally more efficient ways to do searches on numeric columns. To use either the underscore or the percentage sign in a LIKE or NOT LIKE query, you will need to escape them (by preceding the character with a backslash) so that they are not confused as wildcards. The underscore can be used in combination with itself, so, as an example, LIKE '__' would find any two-letter combination.
|
