Simple text searching using LIKE

Consider this SQL statement:

SELECT * FROM usertable WHERE FirstName = 'Paul' OR FirstName = 'Paula' OR FirstName = 'Pauline';

Here we're trying to match the FirstName field against several possible variants of names beginning with "Paul" - there are probably more variants, in which case the query would need to be longer. There is a much better alternative available in the LIKE operator - it allows simple pattern matching to be performed on each row, and therefore allows you to be more flexible in your searches.

For example, the % sign means "any number of characters" (including no characters), which means we could replace the above query with this:

SELECT * FROM usertable WHERE FirstName LIKE 'Paul%';

That would match all names starting with "Paul", case insensitive. We can even use "%Paul%" to match Paul, Paula, John Paul, etc - any number of characters, followed by "Paul", followed by any number of characters.

There is also the _ sign, which means "match any single character", which means that "p_p" matches PHP, pup, pip, pop, and even p_p - it matches everything including itself. Both of these symbols, % and _, can be matched against if you escape them. For example, if you are looking to match any number of characters followed by %hello%, you would need this query:

SELECT * FROM usertable WHERE FirstName LIKE '%\%hello\%%';

Note that _ and % do not match NULL.

One important thing to note is that the LIKE operator will attempt to use any indexes you have on the field, however the index is ignored when you use queries that begin with a wild card - Paul% is much faster than %Paul%.

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Advanced text searching using full-text indexes >>

Previous chapter: Managing indexes

Jump to:

Home: Table of Contents