9.3.17 Simple text searching using LIKEThis is NOT the latest copy of this book; click here for the latest version.
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 see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
