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?
Add a comment
|
6 Answers
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
1 Comment
ThinkCode
Binary is the key here! Thank you!
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.
2 Comments
mdoyle
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.
ThinkCode
Binary is essential in this case (atleast my case). Still upvoting for info.
Try this:
SELECT Name
FROM table
WHERE Name COLLATE latin1_general_cs LIKE UPPER(Name)
;
Comments
Use Below:
SELECT name FROM table WHERE name = BINARY UPPER(column_name);
Comments
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))
1 Comment
Tom
This only works with binary data types or the correct collation.