Home > OS >  How to write a SQL query to print a,A if column values are a,A,A,A,a,A,a,A,a,a,a,A
How to write a SQL query to print a,A if column values are a,A,A,A,a,A,a,A,a,a,a,A

Time:03-08

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

CodePudding user response:

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.

CodePudding user response:

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:- enter image description here

CodePudding user response:

You can also do:

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

If using ASCII character set.

CodePudding user response:

select distinct(XYZ) from Letters

where Letters is the name of table.

  • Related