Database Articles
  Home arrow Database Articles arrow Page 3 - Pattern Matching with Strings
Codewalker Forums 
  Tutorials  
Database Articles  
Miscellaneous  
Navigation Usability  
PEAR Articles  
Programming Basics  
Server Administration  
XML Tutorials  
  Reviews  
Database Book Reviews  
Linux Book Reviews  
Miscellaneous Reviews  
PHP Book Reviews  
PHP Software Reviews  
Server Admin Reviews  
SQL Tool Reviews  
  Code Gallery  
Content Management Code  
Contest Code  
Counters Code  
Database Code  
Date Time Code  
Discussion Board Code  
Email Code  
File Manipulation Code  
GUI Code  
Link Farm Code  
Miscellaneous Code  
Search Code  
Site Navigation Code  
User Management Code  
Forums Sitemap 
Dedicated Servers  
Download TestComplete 
JMSL Numerical Library 
IBM® developerWorks
Weekly Newsletter 
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE ARTICLES

Pattern Matching with Strings
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 2
    2007-09-13

    Table of Contents:
  • Pattern Matching with Strings
  • Pattern Matching with Regular Expressions continued
  • 5.12 Controlling Case Sensitivity in Pattern Matching
  • 5.13 Breaking Apart or Combining Strings
  • Breaking Apart or Combining Strings

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Pattern Matching with Strings - 5.12 Controlling Case Sensitivity in Pattern Matching


    (Page 3 of 5 )

    Problem

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

    Solution

    Alter the case sensitivity of the strings.

    Discussion

    The case sensitivity of a pattern match operation 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.9 for discussion of how these factors apply to comparisons.

    The default character set and collation are latin1 and latin1_swedish_ci, so pattern match operations are not case-sensitive by default:

      mysql> SELECT 'a' LIKE 'A', 'a' REGEXP 'A';
     
    +--------------+----------------+
      | 'a' LIKE 'A' | 'a' REGEXP 'A' |
      +--------------+----------------+
      |            1 |              1 |
      +--------------+----------------+

    Note that a REGEXP operation that is not 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 from what you want, control it the same way as for string comparisons: convert the strings to binary or nonbinary as necessary or change the collation of nonbinary strings.

    To make a pattern match case-sensitive, use a case-sensitive collation for either operand. For example, with the latin1 character set, use a collation of latin1_general_cs:

      mysql> SET @s = 'a' COLLATE latin1_general_cs;
     
    mysql> SELECT @s LIKE 'A', @s REGEXP 'A';
      +-------------+---------------+
      | @s LIKE 'A' | @s REGEXP 'A' |
      +-------------+---------------+
      |           0 |             0 |
      +-------------+---------------+

    Use of a case-sensitive collation also has the effect of causing [:lower:] and [:upper:] in regular expressions to match only lowercase and uppercase characters, respectively. The second expression in the following statement yields a result that really is true only for uppercase letters:

      mysql> SET @s = 'a', @s_cs = 'a' COLLATE latin1_general_cs;
     
    mysql>
    SELECT @s REGEXP '[[:upper:]]', @s_cs REGEXP '[[:upper:]]';

     

     @s REGEXP '[[:upper:]]'  @s_cs REGEXP '[[:upper:]]' 

     

     1  0

     

     

    More Database Articles Articles
    More By O'Reilly Media


     

    Buy this book now. This article is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Check it out today at your favorite bookstore. Buy this book now.

    DATABASE ARTICLES ARTICLES

    - More on Query Optimization for Oracle Databa...
    - Query Optimization in Oracle
    - Clusters and Other Data Structures for Oracle
    - Using Indexes with an Oracle Database
    - The Basics of Data Structures in Oracle
    - Oracle Data Structures
    - Best Practices for PL/SQL Variables
    - What`s Code Without Variables?
    - Clauses, Sorting, and SQL Queries
    - The From Clause and SQL Queries
    - Query Primer
    - Full Text Searches and Strings
    - Searching with Strings
    - Pattern Matching with Strings
    - Working with Cases of Strings






    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway