Case sensitivity of SQL selects

Hi all,
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface ) ?
Thanks
Roshan
Assuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan
|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>