Working with Strings - MySQL - page 160

mysql> SELECT NULL LIKE '%', NULL NOT LIKE '%';
+---------------+-------------------+
| NULL LIKE '%' | NULL NOT LIKE '%' |
+---------------+-------------------+
| NULL | NULL |
+---------------+-------------------+
In some cases, pattern matches are equivalent to substring comparisons. For example,
using patterns to find strings at one end or the other of a string is like using LEFT() or
RIGHT() , as shown in the following table:
Pattern match Substring comparison
str LIKE 'abc%' LEFT( str ,3) = 'abc'
str LIKE '%abc' RIGHT( str ,3) = 'abc'
If you're matching against a column that is indexed and you have a choice of using a
pattern or an equivalent LEFT() expression, you'll likely find the pattern match to be
faster. MySQL can use the index to narrow the search for a pattern that begins with a
literal string. With LEFT() , it cannot.
Case sensitivity of a pattern match is like that of a string comparison. That is, it depends
on whether the operands are binary or nonbinary strings, and for nonbinary strings, it
depends on their collation. See Recipe 5.7 for discussion of how these factors apply to
comparisons.
Using Patterns with Nonstring Values
Unlike some other database systems, MySQL permits pattern matches to be applied to
nonstring values such as numbers or dates, which can sometimes be useful. The fol‐
lowing table shows some ways to test a DATE value d using function calls that extract date
parts and using the equivalent pattern matches. The pairs of expressions are true for
dates occurring in the year 1976, in the month of April, or on the first day of the month:
Function value test Pattern match test
YEAR(d) = 1976 d LIKE '1976-%'
MONTH(d) = 4 d LIKE '%-04-%'
DAYOFMONTH(d) = 1 d LIKE '%-01'
5.9. Pattern Matching with Regular Expressions
Problem
You want to perform a pattern match, not a literal comparison.