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
