0

I have a table in that I have only one column let say column name is XYZ and in that I have following values

a
a
A
a
A
A
a

I want a SQL query that should return me output as below:

a
A
Dale K
  • 25,246
  • 15
  • 42
  • 71
Shorya Sharma
  • 33
  • 2

4 Answers4

4

You need to use case sensitive collation.
Following query will show you how to define collation for distinct select

select distinct XYZ COLLATE SQL_Latin1_General_CP1_CS_AS from [your table]

The "CS" at the end of collation name defines that operations on this column should be case sensitive.

Piotr Palka
  • 3,086
  • 1
  • 9
  • 17
  • This query running perfect as i received expected output Thank u.. "select distinct XYZ COLLATE SQL_Latin1_General_CP1_CS_AS from letters" –  Mar 08 '22 at 05:05
2

You need to understand why you get a single row if distinct used and why the case sensitive does not work.

SQL Server is, by default, case insensitive; however, it is possible to create a case-sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine if a database or database object is to check its "COLLATION" property and look for "CI" or "CS" in the result.

Source:- https://www.webucator.com/article/how-to-check-case-sensitivity-in-sql-server/#:~:text=SQL%20Server%20is%2C%20by%20default,%22CS%22%20in%20the%20result.

Use collation for case sensitive enable or disable. There is different -2 type of level. (SQL Server check case-sensitivity?, How to do a case sensitive search in WHERE clause (I'm using SQL Server)?)

SQL Server check case-sensitivity?

Declare @tableA table ( cola varchar(50))
insert into @tableA values ('a'),('A'),('b'),('B')

select distinct cola from @tableA

--here you get both
select * from @tableA where cola = 'a'

--here you get only what you passed
select cola  from @tableA where cola = 'a' COLLATE SQL_Latin1_General_CP1_CS_AS --Latin1_General_CS_AI--COLLATE Latin1_General_CS_AS

--here you get different result
select cola COLLATE SQL_Latin1_General_CP1_CS_AS  from @tableA  where cola = 'a'

enter image description here

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
0

You can also do:

select char(xyz) as xyz
from (select distinct ascii(xyz) as xyz from yourtable) a

If using ASCII character set.

tinazmu
  • 3,880
  • 2
  • 7
  • 20
-1
select distinct(XYZ) from Letters

where Letters is the name of table.

Ahmad
  • 12,336
  • 6
  • 48
  • 88
Nidhi257
  • 754
  • 1
  • 5
  • 23
  • Your query works only when collation is not case sesitive. – Meyssam Toluie Mar 08 '22 at 04:58
  • 2
    `DISTINCT` is not a function and does not need brackets, they just confuse people into thinking it's only looking at that column. It operates over the whole resultset, not just the first column – Charlieface Mar 08 '22 at 05:21