In previous posts, I explained Check Primary Key Existence in Table, Reset Identity Column in SQL Server, Insert Values into Identity column, Identity Column in SQL Server , STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server , ASP.Net, C#.
In SQL Server , Installation by default are case insensitive .This means that SQL Server ignores the case of the characters and treats the string 'Dot Net World' equal to the string 'dot net world'.
Let's see a practical example. Suppose we have a table say "BlogName" with following records-
Now If we execute the following query-
SELECT Name FROM BlogName WHERE (Name LIKE '%net%')It will return all record because all rows contains same data and search is case insensitive by default.
Now question is how to make it case sensitive? Answer is very simple. You have to change the collation of above query as shown below-
SELECT Name FROM BlogName WHERE (Name LIKE '%net%' COLLATE Latin1_General_CS_AS)Now, execute the above modified query and see the result-
This is the result which we want.By above method, we change column collation for temporary use. but we can change it's collate permanently. by using following Query-
ALTER TABLE Table1 ALTER COLUMN Column1 VARCHAR(20) COLLATE Latin1_General_CS_ASTo know the collation of the column for any table run following Stored Procedure-
EXEC sp_help BlogNameHere is the result of above query-
I hope this will be helpful for you. I would like to have any feedback from you. Your valuable feedback, question, or comments about this article are always welcome.