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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]