List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 2 2007 7:22pm
Subject:Re: REGEXP Character Classes
View as plain text  
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
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