How to change the Collation of table column to Case insensitive or Case sensitive?
Lets prepare a sample table called DumpData, and insert few records as shown below.
CREATE Table dbo.DumpData(ID INT,Name VARCHAR(50))INSERT INTO dbo.DumpData(ID, Name)VALUES(1, 'abc'),(2, 'AbC'),(3, 'aBc'),(4, 'ABC'),(5, 'abC'),(6, 'aBC'),(7, 'AbC')
Now we have a table, and you can see the table output as shown below that contains some string values with upper case, some in lower case or some values are combine of upper and lower characters.
SELECT * FROM dbo.DumpData
Suppose you want to see only those names that have exact match with value ‘abc’, as all characters in ‘abc’ are in lower case, so you want to see all name which contains values ‘abc’ in lower case. Basically you want case sensitive search that returns exact matching records from table.
Lets see, what happens when you execute the following query.
SELECT * FROM dbo.DumpData WHERE [NAME]= 'abc'
You can see for search value ‘abc’, it returns seven matching values which are either in upper case, lower case or mix. While for ‘abc’ there are only single record in table.
It means case senstivitiy is not checked on column so query returns all records contains ‘abc’ only, does not matter whether it is in upper case, lower case, or mix .
It happens because of default Collation of the SQL Server installation that is SQL_Latin1_General_CP1_CI_AI, that is not case sensitive.
Lets check the collation for column Name, using following query that check the collation for all columns in a SQL Server database table. Here we check for table DumpData.
SELECT COLUMN_NAME, COLLATION_NAMEFROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'DumpData' AND CHARACTER_SET_NAME IS NOT NULL
As you can see, the colloation for column Name is Latin1_General_CI_AI, which is case insensitive.
CI in Latin1_General_CI_AI represents case insensitive.
To make query case sensitive and return only ‘abc’ from table, you can modify above query as following
SELECT * FROM dbo.DumpData WHERE [NAME] COLLATE Latin1_General_CS_AS = 'abc'
And you can see the output of query, it returns all single records which is exact match with search value ‘abc’. Means this time case sensitive is applied on column Name.
You can also make column case sensitive by changing column’s collation from case insensitive SQL_Latin1_General_CP1_CI_AI to case sensitive Latin1_General_CS_AS.
Lets use Alter Command to change the column Name collation from case insensitive to case sensitive.
ALTER TABLE dbo.DumpDataALTER COLUMN Name VARCHAR(50)COLLATE Latin1_General_CS_AS
Lets check, whether the column collation is changed or not, by executing same query that we used above to check collation for all columns in a SQL Server table.
SELECT COLUMN_NAME, COLLATION_NAMEFROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'DumpData' AND CHARACTER_SET_NAME IS NOT NULL
You can see, now column collation has been changed to case Sensitive Latin1_General_CS_AS.
CS in Latin1_General_CS_AS represents Case Sensitive.
Now you can use simple query to search for case sensitive value for column Name.
Lets execute the below queries and see the output.
SELECT * FROM dbo.DumpDataWHERE [NAME] = 'abc'SELECT * FROM dbo.DumpDataWHERE [NAME] = 'ABC'SELECT * FROM dbo.DumpDataWHERE [NAME] = 'AbC'
You can see, now case sensitive search is performed on column name and returns exact match for search values.
