[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1163
  • Last Modified:

Is there a way to case sensitive DISTINCT query?

Hi,
My database is set to be case insensitive.
Is there a way to case sensitive DISTINCT query?
i.e.
My table Employees has columns Name and Email
Name       Login
George   10 AM
Tom        10 AM
Tom        11 AM
Tom        12 PM
TOM      10 AM
TOM      11 AM
TOM      1PM

When i query select distinct name from Employess, the result set is
George, Tom as expected.

How can I query so that result set is George, Tom, TOM?

Thanks
Jamie
0
jamie_lynn
Asked:
jamie_lynn
  • 2
1 Solution
 
Jinesh KamdarCommented:
What DB are you using?
0
 
ursangelCommented:
Im guesing its SQL server...
A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters. By default SQL Server 2000 gets installed with case insensitive collation. You must change the collation of the server while installing, if you want case sensitiveness.
The work around for this is to convert the columns that need to be compared as Case sensitive into varbinary and do the comaprison.

select  distinct cast(a.name as varbinary(25))name into #Temp from employee a where
                  cast(a.name as varbinary(25)) in
                        (select distinct cast(b.name as varbinary(25))name  from employee b)  
Go

select cast(name as varchar(25)) name from #Temp
0
 
ursangelCommented:
One easy way.

SELECT   distinct Name COLLATE Latin1_General_CS_AS
FROM      Employees
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now