9

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?

ThinkCode
  • 7,841
  • 21
  • 73
  • 92

6 Answers6

37

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

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
6

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.

m_vitaly
  • 11,856
  • 5
  • 47
  • 63
  • 2
    Depends on the collation and data type. If the data type is non-binary and the collation is, e.g., latin1, string comparisons will be case insensitive. So I believe Mark Byers' answer is the most accurate so far. – mdoyle Aug 23 '12 at 19:32
  • Binary is essential in this case (atleast my case). Still upvoting for info. – ThinkCode Aug 23 '12 at 19:42
2

Try this:

select name from table where name=upper(name);
kurast
  • 1,660
  • 3
  • 17
  • 38
1

Try this:

SELECT Name
FROM   table
WHERE  Name COLLATE latin1_general_cs LIKE UPPER(Name)
;
Tom
  • 6,593
  • 3
  • 21
  • 42
1

Use Below: 

SELECT name FROM table WHERE name = BINARY UPPER(column_name);
Omesh
  • 27,801
  • 6
  • 42
  • 51
Kedar Acharekar
  • 31
  • 1
0

This will also return numeric values, but that doesnt look to be an issue for your column name.

SELECT * FROM names WHERE 

ASCII(name) = ASCII(Upper(name))
chrisboustead
  • 1,573
  • 12
  • 17