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 last_name = 'Bluth' 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 conditional. This is where the LIKE and NOT LIKE terms 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 the last-name example, the query I would write would be SELECT * FROM users WHERE last_name LIKE 'Smith%' This query will return all rows 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. To use LIKE 1. | Select all of the records in which the last name starts with Bank (Figure 4.19).
SELECT * FROM users WHERE last_name LIKE 'Bank%'; Figure 4.19. The LIKE SQL term adds flexibility to your conditionals.
| 2. | Select the name for every record whose email address is not of the form something@authors.com (Figure 4.20).
SELECT first_name, last_name FROM users WHERE email NOT LIKE '%@authors.com; Figure 4.20. A NOT LIKE conditional returns records based upon what a value isn't. If I want to rule out certain possibilities, I can use NOT LIKE with the wildcard.
| Tips Queries with a LIKE conditional are generally slower because they can't take advantage of indexes, so use this format sparingly. The wildcard characters can be used at the front and/or back of a string in your queries. SELECT * FROM users WHERE user_name = '_smith%'
Although LIKE and NOT LIKE are normally used with strings, they can also be applied to numeric columns. To use either the literal underscore or the percentage sign in a LIKE or NOT LIKE query, you will need to escape it (by preceding the character with a backslash) so that it is not confused with a wildcard. The underscore can be used in combination with itself; as an example, LIKE '__' would find any two-letter combination. In the next chapter you'll learn about FULLTEXT searches, which are often better than LIKE searches.
|