In this blog post we are going to learn about how to do Case Sensitive SQL Query Search.
If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.
SELECT Column1 FROM Table1 WHERE Column1 = 'casesearch'
To make the query case sensitive and retrieve only one record (“casesearch”) from the above query, the collation of the query needs to be changed as follows.
SELECT Column1 FROM Table1 WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'
Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.
Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.
To change the collation of the any column for any table permanently run following query.
ALTER TABLE Table1 ALTER COLUMN Column1 VARCHAR(20) COLLATE Latin1_General_CS_AS
To know the collation of the column for any table run following Stored Procedure.
EXEC sp_help DatabaseName
Second results set above script will return you collation of database DatabaseName.
Here are few of the related blog posts on the same subject:
- SQL SERVER – Cannot resolve collation conflict for equal to operation
- SQL SERVER – Creating Database with Different Collation on Server
Let me know your feedback about this blog post in the comments section.
Reference: Pinal Dave (https://blog.sqlauthority.com)
120 Comments. Leave new
EXEC sp_help DatabaseName … does not work
i replaced my database name above….it gave me error saying….
The object ‘aim0rodb’ does not exist in database ‘aim0rodb’.
where ‘aim0rodb’ is my database name…
any help……..
Thanks in advance…
EXEC sp_helpdb DatabaseName
You don’t need to give DatabaseName in EXEC sp_help if your object explorer already selected a database by default
You can give TableName in place of DatabaseName if data base is selected like EXEC sp_help
it’s realy useful and helpful for me .thax to u
very useful & helpful as well!!
Very Helpful.Thanks..You are Awesome !
Thanks resolved a problem I had with loading data from an Oracle db.
I have updated a table’s length property using Alter table. However when running sp_help TableName it displays the old value for the length. How do I update this information?
That should not happen
Are you sure you have queried the correct table name?
thanks
Hey,
You are really genius…
Thanks man.
Its really helpful for me….. Thanks a lot
loved it to the point no wasting anyone’s time thank you.
Its in my reference collection.
Thanks
Your notes is Very usefull
hi,
thanks.
this helps me as well.
thanks.
You da man! It worked.
Hi,
the material excellent on the website ……….appreciate ure help …..
I want to know
how to make an existing DB (that contains schema) case-insensitive ,is there a way to make the DB as well as the schema case-insensitive?
Hello Ruchi,
If the database is case sensitive then schema identfiers are case sensitive in t-sql code. For example if a database name TestDB has a table tblTEST then following statement runs successfully:
SELECT * FROM tblTEST
But below statement fails:
SELECT * FROM tbltest
You can change the collation of database by alter database statement as below:
ALTER DATABASE
COLLATE SQL_Latin1_General_CP1_CI_AS
After this the the schema identifiers in t-sql statements are case insensitive and above written both SELECT statements run successfully. But one more thing to consider here is that changing the collation of database does not change the collation of existing objects in that database. New collation will applied as default collation for all objects that are created after collate change.
Regards,
Pinal Dave
I altered my table successfully, but when I select something from that table and group by the specific field, it doesn’t work. Even when I run sp_help it doesn’t show any collation.
EXEC sp_help ‘table_name’
will show you table informations where one of the columns is collation
Really very helpful…….. Thanks
It helped me alot.. Thanks.
gud one.thanq really helping alot.