Controlling Case Sensitivity in Pattern Matching

4.11.1 Problem

A pattern match is case sensitive when you don't want it to be, or vice versa.

4.11.2 Solution

Alter the case sensitivity of the strings.

4.11.3 Discussion

By default, LIKE is not case sensitive:

mysql> SELECT name, name LIKE '%i%', name LIKE '%I%' FROM metal;
+----------+-----------------+-----------------+
| name | name LIKE '%i%' | name LIKE '%I%' |
+----------+-----------------+-----------------+
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 1 | 1 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 1 | 1 |
| silver | 1 | 1 |
| tin | 1 | 1 |
+----------+-----------------+-----------------+

Currently, REGEXP is not case sensitive, either.

mysql> SELECT name, name REGEXP 'i', name REGEXP 'I' FROM metal;
+----------+-----------------+-----------------+
| name | name REGEXP 'i' | name REGEXP 'I' |
+----------+-----------------+-----------------+
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 1 | 1 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 1 | 1 |
| silver | 1 | 1 |
| tin | 1 | 1 |
+----------+-----------------+-----------------+

However, prior to MySQL 3.23.4, REGEXP operations are case sensitive:

mysql> SELECT name, name REGEXP 'i', name REGEXP 'I' FROM metal;
+----------+-----------------+-----------------+
| name | name REGEXP 'i' | name REGEXP 'I' |
+----------+-----------------+-----------------+
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 1 | 0 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 1 | 0 |
| silver | 1 | 0 |
| tin | 1 | 0 |
+----------+-----------------+-----------------+

Note that the (current) behavior of REGEXP not being case sensitive can lead to some unintuitive results:

mysql> SELECT 'a' REGEXP '[[:lower:]]', 'a' REGEXP '[[:upper:]]';
+--------------------------+--------------------------+
| 'a' REGEXP '[[:lower:]]' | 'a' REGEXP '[[:upper:]]' |
+--------------------------+--------------------------+
| 1 | 1 |
+--------------------------+--------------------------+

Both expressions are true because [:lower:] and [:upper:] are equivalent when case sensitivity doesn't matter.

If a pattern match uses different case-sensitive behavior than what you want, control it the same way as for string comparisons:

  • To make a pattern match case sensitive, use a binary string for either operand (for example, by using the BINARY keyword). The following query shows how the non-binary column name normally is not case sensitive:

    mysql> SELECT name, name LIKE '%i%%', name REGEXP 'i' FROM metal;
    +----------+------------------+-----------------+
    | name | name LIKE '%i%%' | name REGEXP 'i' |
    +----------+------------------+-----------------+
    | copper | 0 | 0 |
    | gold | 0 | 0 |
    | iron | 1 | 1 |
    | lead | 0 | 0 |
    | mercury | 0 | 0 |
    | platinum | 1 | 1 |
    | silver | 1 | 1 |
    | tin | 1 | 1 |
    +----------+------------------+-----------------+

    And this query shows how to force name values to be case sensitive using BINARY:

    mysql> SELECT name, BINARY name LIKE '%I%', BINARY name REGEXP 'I' FROM metal;
    +----------+------------------------+------------------------+
    | name | BINARY name LIKE '%I%' | BINARY name REGEXP 'I' |
    +----------+------------------------+------------------------+
    | copper | 0 | 0 |
    | gold | 0 | 0 |
    | iron | 0 | 0 |
    | lead | 0 | 0 |
    | mercury | 0 | 0 |
    | platinum | 0 | 0 |
    | silver | 0 | 0 |
    | tin | 0 | 0 |
    +----------+------------------------+------------------------+

    Using BINARY also has the effect of causing [:lower:] and [:upper:] in regular expressions to act as you would expect. The second expression in the following query yields a result that really is true only for uppercase letters:

    mysql> SELECT 'a' REGEXP '[[:upper:]]', BINARY 'a' REGEXP '[[:upper:]]';
    +--------------------------+---------------------------------+
    | 'a' REGEXP '[[:upper:]]' | BINARY 'a' REGEXP '[[:upper:]]' |
    +--------------------------+---------------------------------+
    | 1 | 0 |
    +--------------------------+---------------------------------+
  • A pattern match against a binary column is case sensitive. To make the match not case sensitive, make both operands the same lettercase. To see how this works, modify the metal table to add a binname column that is like the name column except that it is VARCHAR BINARY rather than VARCHAR:

    mysql> ALTER TABLE metal ADD binname VARCHAR(20) BINARY;
    mysql> UPDATE metal SET binname = name;

    The first of the following queries shows how the binary column binname normally is case sensitive in pattern matches, and the second shows how to force it not to be, using UPPER( ):

    mysql> SELECT binname, binname LIKE '%I%', binname REGEXP 'I'
     -> FROM metal;
    +----------+--------------------+--------------------+
    | binname | binname LIKE '%I%' | binname REGEXP 'I' |
    +----------+--------------------+--------------------+
    | copper | 0 | 0 |
    | gold | 0 | 0 |
    | iron | 0 | 0 |
    | lead | 0 | 0 |
    | mercury | 0 | 0 |
    | platinum | 0 | 0 |
    | silver | 0 | 0 |
    | tin | 0 | 0 |
    +----------+--------------------+--------------------+
    mysql> SELECT binname, UPPER(binname) LIKE '%I%', UPPER(binname) REGEXP 'I'
     -> FROM metal;
    +----------+---------------------------+---------------------------+
    | binname | UPPER(binname) LIKE '%I%' | UPPER(binname) REGEXP 'I' |
    +----------+---------------------------+---------------------------+
    | copper | 0 | 0 |
    | gold | 0 | 0 |
    | iron | 1 | 1 |
    | lead | 0 | 0 |
    | mercury | 0 | 0 |
    | platinum | 1 | 1 |
    | silver | 1 | 1 |
    | tin | 1 | 1 |
    +----------+---------------------------+---------------------------+

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

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