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?


--------------
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)

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=paul@mysql.com


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com