35

I want to check if a string consists only of uppercase letters. I know that RLIKE/REGEXP are not case sensitive in MySQL, so I tried to use the :upper: character class:

SELECT 'z' REGEXP '^[[:upper:]]+$';

This gives true, although the z is in lower case. Why is that?

radhadman
  • 115
  • 8
R_User
  • 10,682
  • 25
  • 79
  • 120

4 Answers4

65

REGEXP is not case sensitive, except when used with binary strings.

http://dev.mysql.com/doc/refman/5.7/en/regexp.html

So with that in mind, just do something like this:

SELECT * FROM `users` WHERE `email` REGEXP BINARY '[A-Z]';

Using the above example, you'd get a list of emails that contain one or more uppercase letters.

Kyle Anderson
  • 3,059
  • 1
  • 13
  • 19
26

For me this works and is not using a regexp. It basically compares the field with itself uppercased by mysql itself. 

-- will detect all names that are not in uppercase
SELECT 
    name, UPPER(name) 
FROM table 
WHERE 
    BINARY name <> BINARY UPPER(name)
;
Elzo Valugi
  • 27,240
  • 15
  • 95
  • 114
  • This solved for me, just wanted to check if there was ANY upper case letter in my column values. Replaced the `UPPER` in the answer for `LOWER`. – Maximiliano Guerra Oct 30 '18 at 13:57
  • 1
    To explain why this works, String comparisons in MySQL are case insensitive. That means the words 'XYZ' and 'xyz' are treated equal during comparisons. If we compare using BINARY, exact characters are compared. – Praveen E Jan 19 '21 at 10:16
7

change to case sensitive collation, eg.

CHARACTER SET latin1 COLLATE latin1_general_cs

then try this query,

SELECT 'z' REGEXP '^[A-Z]+$'
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • +1 had the same idea but didn't know the trick with latin1 character set – Stephan May 15 '13 at 07:22
  • So, but why does the character class not work? Because I'm using the wrong collation? why adding `:upper:` when `A-Z` does the same, and `:upper:` is the same as `:lower:` when the collation is `*_ci`? I thought `*_ci` gives the dafault, but when using `:upper:` it looks for upper case letters. In the description of [:character_class:] on http://dev.mysql.com/doc/refman/5.0/en/regexp.html It says I should look at the manual pages of `ctype(3)`. I tried `help ctype`, `man ctype`,... nothing worked – R_User May 15 '13 at 07:40
  • I tried: `SELECT 'z' RLIKE '^[A-Z]$' COLLATE 'utf8_general_cs';`, which gave me an error: `ERROR 1273 (HY000): Unknown collation: 'utf8_general_cs'`. The command `SELECT 'z' RLIKE '^[A-Z]$' COLLATE 'utf8_general_ci';` works fine. So how can i install the case sensitive collation? – R_User May 15 '13 at 08:04
  • Found a solution for the error: `SELECT CONVERT(_utf8'z' USING 'latin1') RLIKE CONVERT(_utf8'[[:upper:]]' USING 'latin1') COLLATE 'latin1_general_cs';` Is this really the only way to introduce case-sensitivity to MySQL? – R_User May 15 '13 at 10:33
  • How about this, without regex. `SELECT * FROM table where column = upper(column);` – Noel May 15 '13 at 11:50
1

The most voted answer doesn't work for me, I get the error:

Character set 'utf8mb4_unicode_ci' cannot be used in conjunction with 'binary' in call to regexp_like.

I used the MD5 to compare the original value and the lowercased value:

SELECT * FROM user WHERE MD5(email) <> MD5(LOWER(email));
Hans Ienasescu
  • 11
  • 1