Case Sensitive Select
-
Is there any way on a non-case-sensitive SQL Server, to do a "Select Distinct..." which will bring back values differentiated only by the case of their alpha characters?
For example, lets say you have a table with four records that look like this:
C0c25
C0C25
C0c25
c0c25
I want to do a "Select Distinct..." which returns the following result set:
C0c25
C0C25
c0c25
Any ideas on how to do this?
-
Hopefully someone will know of system setting or other easier method.
In case no one else thinks of anything, it seems to me you may have to do a fairly sizeable loop. I would suggest looping through each record and converting each character to ASCII. You can then use a running SUM of those ASCII values for each record. Once you have gone through the entire record, save the original value and the running sum to a #TempTable. Use a WHERE NOT EXIST( SELECT runningsum FROM #TempTable) to ensure only DISTINCT records are being saved.
As I said, hopefully someone will give you an easier answer than this bulldozer approach.
I wasn't born stupid - I had to study.
-
This isn't bullduzer but I'm not sure it's the fastest approach, but that works so :
Select cast(dtName.Name as varchar(100)) as Name from
(Select distinct(cast(name as varbinary)) as Name from dbo.ObjSQL where name = 'name') dtName
here's how it works :
Select cast('A' as varbinary)
union
Select cast('a' as varbinary)
2 row(s) affected
0x41
0x61
the distinct is then evaluated on the binary values so case sensitive matches are removed, then you simply cast back to varchar and voila!
-
Definately the way to do it.
Select those values into a #TempTable using the WHERE NOT EXISTS(.... clause, then select all values from the #TempTable converting them back to varchar.
SELECT CAST( 'A0a25' AS varbinary), CAST( 'a0a25' AS varbinary)
SELECT CAST( CAST( 'A0a25' AS varbinary) AS varchar), CAST( CAST( 'a0a25' AS varbinary) AS varchar)
Thanks Remi
I wasn't born stupid - I had to study.
-
Why not simply use the power of SQL 2K to perform this for you i.e.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTest]
GO
CREATE TABLE [dbo].[tblTest] (
[Field1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO tblTest VALUES ('C0c25')
INSERT INTO tblTest VALUES ('C0C25')
INSERT INTO tblTest VALUES ('C0c25')
INSERT INTO tblTest VALUES ('c0c25')
SELECT DISTINCT Field1 COLLATE SQL_Latin1_General_CP1_CS_AS FROM tblTest
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Neat trick AJ. It works faster than my version and is much easier to understand.
One less thing to learn today
-
Perfect, AJ! That's exactly what I was looking for.
Thanks!
-
The only thing y ou would have to do with this though is do a WHERE CLAUSE because in the real world you you may have a value of Q in field1
WHERE UPPER([Field1])='C0C25'
-
Chris,
Having SQL Server differentiate between different alpha characters was NOT the problem. What I wanted was for SQL Server to differentiate between identical values that differ only in the case of their alpha characters. I wanted SQL Server to see "C0C25" as different from "C0c25" and different from "c0c25", and return all three of those values from a "Select Distinct" query.
The UPPER function is less than useless in this case. First, because it would have absolutely no effect being in the WHERE clause, where it would only limit the number records to select from. Second, because it would cause SQL Server to see all different alpha cases as the same, which is the exact opposite of what I wanted.
Peace.
-
Edwin,
I thought you were looking for Distinct values of a "C0c25". I did not understand that you were just wanting to see all DISCTINCT values... Sorry I offended.
God Speed..
-
I wasn't offended, but as a fellow professional I didn't want you to be under the impression that the solution you put forward would return the result set that I described in the original post. I didn't realize that you had misunderstood the results I wanted to see.
It's about the work. It's not personal.
Peace.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply