hi all,
i want to ask if there is a way to do a case sensitive search in SQL queries, for example
Select * from usrs where pwd='somevalue'// is there any fucntion i can use here to force a case sensitive match
thanks

Yes use COLLATE SQL_Latin1_General_CP1_CS_AS, take a look at the following example

create TABLE dbo.Customers

(
            CustID char(8) NOT NULL ,
            CustPassword varchar(15) NOT NULL,
            CustName varchar(25) NOT NULL,
            CustEmail varchar(60) NOT NULL,
            CustAddress varchar(100) NOT NULL
)

GO


INSERT INTO dbo.Customers VALUES('USA00001', 'TheUnbreakable', 'John', '[email protected]', '10932,Bigge Rd., Menlo Park, CA')
INSERT INTO dbo.Customers VALUES('USa00001', 'ABCxyz012789', 'Heather', '[email protected]', '18 Broadway Av. San Francisco, CA')
INSERT INTO dbo.Customers VALUES('EUR00001', 'MagicNSparkle', 'Vyas', '[email protected]', '18 Grove Mead, Herts, AL09 7JK, UK')
INSERT INTO dbo.Customers VALUES('ASA00001', 'aAbBcCXyZ', 'Franklin', '[email protected]', '22 Main Street, Singapore')
INSERT INTO dbo.Customers VALUES('OZ000001', 'DOWNundah', 'Steve', '[email protected]', '321, Cricket Way, Melbourne, Australia')

SELECT *
            FROM dbo.Customers
            WHERE CustID = 'USA00001' COLLATE SQL_Latin1_General_CP1_CS_AS

SELECT *
            FROM dbo.Customers
            WHERE CustID = 'USA00001'

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

As I recall there is also a setting to turn case sensitivity on at the database level but its turned off as default. Might also want to check on this if you don't want to do the stuff with Collate and specify languages. But I may be thinking of a different RDBMS so verify it for yourself in your server settings.

10x guys for the help it has been a pleasure working with you, wish you both good luck

Hi,

Either you can go for COLLATION for case sensitive match other wise the better and handy way is to use ASCII value comparison. This aproach doesn't change your SQL Collation and transparent to other users. See ASCII ndton in T-SQL.

Hope you got the idea.

B. Anant

thanks B. Anant for your extra information, although the above replies helped a lot, however extra information is not bad, but could you please give an example?
also what does ndton mean?

Sometimes an example is worth a 1000 words

Use Northwind
GO
Select SERVERPROPERTY(N'Collation')
/*
SQL_Latin1_General_CP1_CI_AS -- the collation I am using
SQL_Latin1_General_CP1_CS_AS -- the collation I will use for the comparison
*/

--Setup Data for Example
UPDATE dbo.Customers SET ContactTitle = 'OWNer'
    WHERE CustomerID = 'ANATR'

--Case Insensitive Search
SELECT CustomerID, ContactTitle
FROM dbo.customers
WHERE contactTitle = 'OWNer'

--Case Sensitive Search -- that won't find our test data
SELECT CustomerID, ContactTitle
FROM dbo.customers
WHERE contactTitle COLLATE SQL_Latin1_General_CP1_CS_AS = 'Owner'

--Case Sensitive Search -- that will find our test data
SELECT CustomerID, ContactTitle
FROM dbo.customers
WHERE contactTitle COLLATE SQL_Latin1_General_CP1_CS_AS = 'OWNer'

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

it is true that an example worth 1000 words especially if it is perfect, i will try it and tell tell you the results(although sure that it will work fine),Anyway thanks David for your great help, that twice you came for my rescue and i appreciate that.

Anantsharma,
ASCII works only 1 character at a time meaning select ascii('ab'),ascii('ac') returns 97 in both cases, you would have to loop thru the whole string
And the ASCII character set only has 255 characters what about non ASCII characters?

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/