SQL SERVER - Collate - Case Sensitive SQL Query Search « Journey to SQL Authority with Pinal Dave

SQL SERVER - Collate - Case Sensitive SQL Query Search

April 30, 2007 by pinaldave

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 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.

Posted in SQL, SQL Authority, SQL Cursor, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 27 Comments

27 Responses to “SQL SERVER - Collate - Case Sensitive SQL Query Search”

on April 30, 2007 at 6:26 pm anonymous

This is good. I saved it.
on June 9, 2007 at 1:40 am udhaya

it was very useful.
on June 14, 2007 at 1:03 pm Carlo

This helped resolve a case sensitivity issue with a DB application I was testing, a customer’s database collation was set to LATIN1_GENERAL_BIN which prevented our DB application from running it’s case variable statements thanks! :)
on July 5, 2007 at 12:29 am Julie

Hey,,

Really it’s good men………
Very Helpful too…..
on July 15, 2007 at 10:53 pm Ragesh

its very helpful

Thanks
Regards
Ragesh
on August 31, 2007 at 8:54 am Saeed Siddiqui

Dear Pinal
its not working, im using sql server 2005 and entered the below information:

SELECT Col2
FROM Table_1
WHERE Col2 COLLATE Latin1_General_CS_AS = ‘saeed’

but its give me err :

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘‘’.
on October 1, 2007 at 2:30 pm Kida

Thanks so much for the help… this was excactly what I needed.

Had I just found this page first instead… :)
on December 19, 2007 at 1:44 pm Anand K

Hi Dave,

Thank you very much for this help.This is the thing i needed.

Thank you once again
on February 6, 2008 at 12:29 pm Praveen

Thanks. Its working with SQLServer 2000
on February 19, 2008 at 4:22 pm ranjan

thanks brother

for your blogs
on March 19, 2008 at 7:28 am Justin J. Moses

v. handy.

thanx Pinal
on March 27, 2008 at 1:23 pm Nandan Mathure

Hi,

I am working on an application where end user will define his own collation. Can collation be specified on fly without using sp_executesql function.
on April 5, 2008 at 4:56 am Charlie

Brilliant! Thanks for finding this and getting out here where I could find it.

And did Saeed figure the problem was the wrong character for quoting text (‘saeed’ vs. ’saeed’)?
on May 15, 2008 at 1:39 am Ahliana Byrd

You rock. THANKS. You solved what I was being told couldn’t really be solved. Excellent!
on May 23, 2008 at 7:13 pm HEMANT VERMA

very helpful….thank you very much.
on June 2, 2008 at 8:17 pm Deepak Joshi

Thanks, Pinal.
It is a very useful piece of information.
on June 5, 2008 at 3:17 pm ralpharama

Thanks for that useful page.
on June 17, 2008 at 1:23 pm MasterHoon

very useful. thanks!
on June 20, 2008 at 8:05 am shravan

it is to much helpful for me
on June 20, 2008 at 8:06 am shravan

Thanks
it is to much helpful for me
on August 8, 2008 at 4:40 pm Vallabh

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…
on August 26, 2008 at 2:23 pm Koteswara Rao

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
on September 4, 2008 at 11:23 am Poonam Gupta

it’s realy useful and helpful for me .thax to u
on September 8, 2008 at 4:16 pm Vipul

very useful & helpful as well!!
on September 8, 2008 at 11:04 pm kamn

Very Helpful.Thanks..You are Awesome !
on October 1, 2008 at 5:17 pm Sandy

Thanks resolved a problem I had with loading data from an Oracle db.
on December 1, 2008 at 11:10 pm Tina

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?