How to find all upper case strings in a MySQL table?

Question

I initially thought this is trivial. Then thought 'binary' might do it. I am unsure at this point.

Name
----
John
MARY
Kin
TED

I would like to query just MARY and TED which are in all upper case. How would I query this?

Answer 1

If your collation is case insensitive then you need to use a BINARY comparison:

SELECT *
FROM yourtable
WHERE Name = BINARY UPPER(Name)

See it working online: sqlfiddle

Answer 2

You just use the UPPER() function on the Name field and compare the results with the original value of Name:

select Name from Table where Name = UPPER(Name)

This way

UPPER(Name)   ||  Name
---------------------------------------
JOHN          !=  John
MARY          ==  MARY
KIN           !=  Kin
TED           ==  TED

only the rows you need will be returned.

As @mdoyle commented here, you should define the column with the right collation (case sensitive), otherwise as others did answer you need the BINARY operator to compare case insensitive columns.

Answer 3

Try this:

select name from table where name=upper(name);

Answer 4

Try this:

SELECT Name
FROM   table
WHERE  Name COLLATE latin1_general_cs LIKE UPPER(Name)
;