SEO

I went to the MySQL documentation pages and read up on using COLLATE. I
knew SELECT was case-insensitive, but I was sort of surprised that using
a character class didn't override that. Anyway, I next tried the
"status" command to see if it gave me any characterset information.

Client characterset: latin1
Server characterset: latin1

Once I thought I understood what was going on with COLLATE and case
sensitivity, I tried this command...

SELECT id, pswd, division, department, title, classification FROM pswds
WHERE pswd REGEXP '[:lower:]' COLLATE latin1_bin;

It seemed to work fine. I searched the column to see if I could find any
instances of all caps value, but did not find any. (They do exist; I
created the data for this table from a Perl script solely to practice
using character class regular expressions.)

Then I tried this command. It should not have found any instances of all
lower case passwords, but it did.

SELECT id, pswd, division, department, title, classification FROM pswds
WHERE pswd REGEXP '[:upper:]' COLLATE latin1_bin;
+------+----------+------------------+------------+------------+----------------+
| 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
|


Is there something obvious that I'm missing here?

At 5:33 AM -0400 5/2/07, John Kebbel wrote:
>I went to the MySQL documentation pages and read up on using COLLATE. I
>knew SELECT was case-insensitive, but I was sort of surprised that using
>a character class didn't override that. Anyway, I next tried the
>"status" command to see if it gave me any characterset information.
>
>Client characterset: latin1
>Server characterset: latin1
>
>Once I thought I understood what was going on with COLLATE and case
>sensitivity, I tried this command...
>
>SELECT id, pswd, division, department, title, classification FROM pswds
>WHERE pswd REGEXP '[:lower:]' COLLATE latin1_bin;
>
>It seemed to work fine. I searched the column to see if I could find any
>instances of all caps value, but did not find any. (They do exist; I
>created the data for this table from a Perl script solely to practice
>using character class regular expressions.)
>
>Then I tried this command. It should not have found any instances of all
>lower case passwords, but it did.
>
>SELECT id, pswd, division, department, title, classification FROM pswds
>WHERE pswd REGEXP '[:upper:]' COLLATE latin1_bin;
>+------+----------+------------------+------------+------------+----------------+
>| 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
>|
>
>Is there something obvious that I'm missing here?

Ah. Yes. I should have noticed this in your earlier message, sorry:
The character class names *include* the square brackets, so to use
them as part of a range, you need another set of square brackets,
i.e., [[:upper:]].

What your statement looks for is any values containing :, u, p, e, or r,
and that's why you see the values returned that you do.

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