Re: REGEXP Character Classes
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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REGEXP Character Classes
I was experimenting with Character Classes because they were covered in MySQL Crash Course. There are probably substitutes for all the character classes--such as ^[a-z] for [:lower:]--that I probably should stick with instead of wandering off into foreign territory. Fooling with Character Classes did pay off, however, because I heard about COLLATE and declaring columns as binary from other responses. On Wed, 2007-05-02 at 08:08 -0400, [EMAIL PROTECTED] wrote: Wouldn't the regular expression be ^[a-z].* ^ = start of string [ a-z] = class range for lower case . = any character * = mods last to grab anything after that... actually you should just be able to get by with ^[a-z] John Kebbel [EMAIL PROTECTED] 05/02/2007 05:33 AM Please respond to [EMAIL PROTECTED] To MySQL mysql@lists.mysql.com cc Subject Re: REGEXP Character Classes 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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REGEXP Character Classes
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]
REGEXP Character Classes
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) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REGEXP Character Classes
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/[EMAIL PROTECTED] -- 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]
