List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 2 2007 12:49am
Subject:Re: REGEXP Character Classes
View as plain text  
At 6:20 PM -0400 5/1/07, John Kebbel wrote:
>Linux Version: Linux version 2.6.15-28-386
>MySQL Version:  5.0.22-Debian_0ubuntu6.06.3-log
>
>I have two queries using REGEXP character classes and their respective
>outputs below. The first is supposed to match an upper case character in
>a column, but I wind up with 4 rows out of 25 that contain only lower
>case characters. The second is supposed to match lower case characters
>but returns 11 rows out of 25 that contain only upper case characters.
>
>Am I using these character classes correctly?

The classes define what characters are members of the class.

But the matching is determined by the collation of the REGEXP
operands.  If you have a case-insensitive collation (which you
do, by default), the matching takes place in a case-insensitive
fashion.

You could apply a COLLATE clause to one of the operands to force
the use of a case-sensitive collation.

>
>
>--------------
>SELECT id, pswd, division, department, title, classification FROM pswds
>WHERE pswd REGEXP '[:upper:]' limit 25;
>--------------
>
>+------+----------+------------------+------------+------------+----------------+
>| id   | pswd     | division         | department | title      |
>classification |
>+------+----------+------------------+------------+------------+----------------+
>|    8 | euwsrbwm | Customer Service | Accounting | Clerical   | 0f1b12
>|
>|   13 | mejccvoz | Customer Service | Receiving  | Clerical   | 437113
>|
>|   18 | kwkheprh | Customer Service | Purchasing | Clerical   | 29652
>|
>|   20 | qpvxvqhz | Customer Service | Accounting | Clerical   | bcb244
>|
>+------+----------+------------------+------------+------------+----------------+
>25 rows in set (0.00 sec)
>
>
>--------------
>SELECT id, pswd, division, department, title, classification FROM pswds
>WHERE pswd REGEXP '[:lower:]' limit 25;
>--------------
>
>+------+----------+------------------+------------+------------+----------------+
>| id   | pswd     | division         | department | title      |
>classification |
>+------+----------+------------------+------------+------------+----------------+
>|    5 | VBOEUTTM | Human Resources  | Purchasing | Clerical   | c18528
>|
>|    9 | ENDPAXWW | Human Resources  | Accounting | Clerical   | 73d00f
>|
>|   14 | TEVXTOBK | Human Resources  | Accounting | Sales Rep. | 6606a0
>|
>|   15 | WREZUFAU | Customer Service | Receiving  | Asst.      | 14159
>|
>|   17 | LGMMPJEY | Customer Service | Accounting | Asst.      | 291512
>|
>|   21 | DMCLWWDX | Customer Service | Receiving  | Sales Rep. | 968745
>|
>|   23 | BZZCQWWE | Customer Service | Payroll    | Asst.      | 11f2b7
>|
>|   24 | EPGWQEXC | Customer Service | Payroll    | Clerical   | 706894
>|
>|   31 | NYOOQVJI | Human Resources  | Accounting | Sales Rep. | e7d0bc
>|
>|   33 | BUTSHOUS | Human Resources  | Payroll    | Asst.      | 548082
>|
>|   34 | VOSCTTGZ | Customer Service | Receiving  | Sales Rep. | 858435
>|
>+------+----------+------------------+------------+------------+----------------+
>25 rows in set (0.00 sec)
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Thread
REGEXP Character ClassesJohn Kebbel2 May
  • Re: REGEXP Character ClassesPaul DuBois2 May
Re: REGEXP Character ClassesJohn Kebbel2 May
  • Re: REGEXP Character ClassesPaul DuBois2 May
Re: REGEXP Character ClassesJohn Kebbel2 May