Select only the upper case or lower case value from sql server table

Many of them would come to a sudden conclusion that SQL server is not a case sensitive, so we can't select only the upper or lower case characters.

But there is an option in sql server to select only the upper case and lower case letters. my below codes will give you a good explanation.

create table #temp(id int, letter char(1))

insert into #temp(id, letter)
values (1 , 'a')

insert into #temp(id, letter)
values (2 , 'b')

insert into #temp(id, letter)
values (3 , 'A')

insert into #temp(id, letter)
values (4 , 'B')

Now the table #temp would be like;

id Letter

1 a
2 b
3 A
4 B

Now, i'm going to write a Query to select only the upper case letters

SELECT id, letter
FROM #Temp
WHERE letter COLLATE SQL_Latin1_General_CP1_CS_AS =
UPPER(letter) COLLATE SQL_Latin1_General_CP1_CS_AS

The above query will give the result like,

id letter
3 A
4 B

Now, i'm going to write a Query to select only the lower case letters

SELECT id, letter
FROM #Temp
WHERE letter COLLATE SQL_Latin1_General_CP1_CS_AS =
LOWER(letter) COLLATE SQL_Latin1_General_CP1_CS_AS

The above query will give the result like,

id letter
1 a
2 b

if you want to select only the upper case value A
, then you can add a condition to the Query like;

SELECT id, letter
FROM #Temp
WHERE letter COLLATE SQL_Latin1_General_CP1_CS_AS =
UPPER(letter) COLLATE SQL_Latin1_General_CP1_CS_AS
AND letter = 'A'

this would return the result like;

id letter
3 A

I hope you would have been enjoyed the trick :-)