sql server case sensitive search compare
SQL Server default installation is case insensitive, which is better for usability other wise you will need to know the exact case of your data before you can search. If you are searching for customers in particular city and data is stored in case sensitive sort order like "New York" "new york" or "New york" you will not be able to retrive all the rows just by WHERE city = 'New York'. There are cases when you want to search and compare data in case sensitive manner, as an example you might have a table with usernames and passwords and as common sense and security, you should have case sensitive compare on password column.
The sort order or case sentivity is controlled by SQL Server collation property, with SQL Server 2000 you can define the sort order at database and column level which makes life easy when you want to change the collation with out reinstalling sql server. To view the current collation at server level you can use the following query.
SELECT SERVERPROPERTY(N'Collation')
There are few methods to perform case sensitive search and compare data, even if you installed sql server as case insensitive.
First method using COLLATE clause in your T-SQL statement.
CREATE TABLE #tmp
(
username varchar(15),
user_pwd varchar(15)
)
INSERT INTO #tmp VALUES('user1', 'PassWord')
SELECT * FROM #tmp
WHERE
username = 'user1'
AND
user_pwd = 'PassWord' COLLATE SQL_Latin1_General_CP1_CS_AS
DROP TABLE #tmp
The second way could be to use binary compare, by converting the column into binary data type using CAST.
CREATE TABLE #tmp
(
username varchar(15),
user_pwd varchar(15)
)
INSERT INTO #tmp VALUES('user1', 'PassWord')
SELECT * FROM #tmp
WHERE
username = 'user1'
AND
CAST(user_pwd AS varbinary(15)) = CAST('PassWord' AS varbinary(15))
DROP TABLE #tmp
If you have permenant need of performing case sensitive search and also case insensitive search then you can add a computed column. This is better then changing the column collation property permenantly as you can perform case sensitive search using computed column and case insensitive search on orignal column.
Computed columns are virtual columns which derives data from existing columns in the same table.
Here is an example of case sensitive search using computed column.
CREATE TABLE #tmp
(
username varchar(15),
user_pwd varchar(15)
)
ALTER TABLE #tmp ADD user_pwd_cs AS (user_pwd COLLATE SQL_Latin1_General_CP1_CS_AS)
GO
INSERT INTO #tmp VALUES('user1', 'PassWord')
SELECT * FROM #tmp
WHERE
username = 'user1'
AND
user_pwd_cs = 'PassWord'
DROP TABLE #tmp
The above methods will give you enough guidence in how to perform case sensitive or case insensitive searches in SQL Server.