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 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.




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net