CODEWITHSUNDEEP
mysqlregexuppercase
R_User
R_User

Reputation: 11082

How to check for uppercase letters in MySQL?

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?

Upvotes: 38

Views: 45373

Answers (4)

Hans Ienasescu
Hans Ienasescu

Reputation: 41

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));

Upvotes: 4

Elzo Valugi
Elzo Valugi

Reputation: 27866

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)
;

Upvotes: 30

Kyle Anderson
Kyle Anderson

Reputation: 3229

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.

Upvotes: 67

John Woo
John Woo

Reputation: 263723

change to case sensitive collation, eg.

CHARACTER SET latin1 COLLATE latin1_general_cs

then try this query,

SELECT 'z' REGEXP '^[A-Z]+$'

Upvotes: 7

Related Questions