Select ALL fields that contains only UPPERCASE letters

Question

How do you select a field that contains only uppercase character in mysql or a field that doesn't contain any lower case character?

Answer 1

You may want to use a case sensitive collation. I believe the default is case insensitive. Example:

CREATE TABLE my_table (    id int,    name varchar(50) ) CHARACTER SET latin1 COLLATE latin1_general_cs;  INSERT INTO my_table VALUES (1, 'SomeThing'); INSERT INTO my_table VALUES (2, 'something'); INSERT INTO my_table VALUES (3, 'SOMETHING'); INSERT INTO my_table VALUES (4, 'SOME4THING'); 

Then:

SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$'; +------+-----------+ | id   | name      | +------+-----------+ |    3 | SOMETHING | +------+-----------+ 1 row in set (0.00 sec) 

If you don't want to use a case sensitive collation for the whole table, you can also use the COLLATE clause as @kchau suggested in the other answer.

Let's try with a table using a case insensitive collation:

CREATE TABLE my_table (    id int,    name varchar(50) ) CHARACTER SET latin1 COLLATE latin1_general_ci;  INSERT INTO my_table VALUES (1, 'SomeThing'); INSERT INTO my_table VALUES (2, 'something'); INSERT INTO my_table VALUES (3, 'SOMETHING'); INSERT INTO my_table VALUES (4, 'SOME4THING'); 

This won't work very well:

SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$'; +------+-----------+ | id   | name      | +------+-----------+ |    1 | SomeThing | |    2 | something | |    3 | SOMETHING | +------+-----------+ 3 rows in set (0.00 sec) 

But we can use the COLLATE clause to collate the name field to a case sensitive collation:

SELECT * FROM my_table WHERE (name COLLATE latin1_general_cs) REGEXP '^[A-Z]+$'; +------+-----------+ | id   | name      | +------+-----------+ |    3 | SOMETHING | +------+-----------+ 1 row in set (0.00 sec) 

Answer 2

This worked for me. It found all user emails with uppercase character:

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