SQL SERVER – Collate – Case Sensitive SQL Query Search

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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

112 thoughts on “SQL SERVER – Collate – Case Sensitive SQL Query Search

  1. 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! :)

    Like

  2. 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 ‘‘’.

    Like

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

    Like

  4. 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’)?

    Like

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

    Like

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

    Like

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

    Like

  8. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 3 Journey to SQL Authority with Pinal Dave

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

    Like

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

    Like

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

    Like

  12. I tried using this example with a like and wildcards, but it still produced results of all data, both upper case and lower case:

    SELECT PropertyState
    FROM Property
    WHERE PropertyState COLLATE Latin1_General_CS_AS like ‘%[a-z]%’

    I just need to find all states with parts of the state text not all uppercase. (i.e. Ct, uT, ca, Id, hI, wa)

    Like

  13. It made simple what others reported to be quite complicate to deal with (cross db query with different collations).
    It helped me, thanks a lot,

    Ste

    Like

  14. Hi,

    How to change the collation of database to Indic_General_90_CS_AS?

    I tried out
    sample is my database.
    ALTER DATABASE sampleCOLLATE Indic_General_90_CS_AS

    while executing this command i m getting error like

    Collation ‘Indic_General_90_CS_AS’ is supported on Unicode data types only and cannot be set at the database or server level.

    Can you help me!!!!!!

    Regards,
    Poongodi

    Like

  15. Hello Pinal and Everyone,
    I am a teacher in London but would like to switch my career to sql programmer as I want to be database developer. I have done MCA and just recently passed my MCTS in sql2005. I am trying hard to get job as a sql programmar. I am applying here many jobs a day but they need experience.I always read your posts on the website.
    can anybody help by giving me some voluntry work by that I can get experience.

    I will very thankful to you.
    waiting for your reply

    Like

  16. Hi,

    We have a similar situation recently and this is very useful. Will there be any impact on the performance if I use collate in where clause?

    Like

  17. Very nice thing I’ve ever found in my Programming Life…. Thanks Bro…

    Md. Sumonur Rahman
    Dot Net Programmer
    Bangladesh

    Like

  18. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31 Journey to SQLAuthority

  19. HI ,I m facing a problem in SQL server plz guide me in that…

    I m having a table in which a primary key is there with 2 columns(CODE nvarchar,VALUE nvarchar).This table contains the values in the Key columns as (X8900,A) but when I try to insert a new value as (X8900,a) ,its giving error message “primary key violation”.
    Why its giving this error,if case is different for values column?

    Like

  20. Just great as usual. I used SQL_Latin1_General_Cp1253_CI_AI instead, so I do not care about capital letters or accents. Before I was using the typical replace instruction but performance began to be affected with big sets

    Like

  21. Hi,

    How does it impact the performance by using collate clause in where conditions of the query?

    How does this differs from using upper or lower functions on both sides of where clause conditions of the query?

    Like

  22. Hi Sir,

    SELECT Column1
    FROM Table1
    WHERE Column1 COLLATE Latin1_General_CS_AS LIKE ‘[A-Z]%’

    th above query is not returning only the value statrs with capital letters why

    Like

  23. This page helped me, but I had a different reason for needing it. The field in the table is already collated, which was preventing me from concatenating it with another string field.

    This statement fails:

    SELECT [ClassId]
    ,[Description]
    ,ClassId + ‘ ‘+ Description as Combo
    FROM [inventory_class]

    because ClassID is a collated field in the structure, but using the collate in the select statement allowed a concatenated combination of fields to be used as parameter list in a report:

    This one works:

    SELECT [ClassId]
    ,[Description]
    ,ClassId COLLATE Latin1_General_CS_AS + ‘ ‘+ Description as Combo
    FROM [inventory_class]

    Like

  24. Pingback: SQL SERVER – Resolve Cannot Resolve Collation Conflict Error – SQL in Sixty Seconds #047 | SQL Server Journey with SQL Authority

  25. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

  26. Hi,

    below query returns result in both the cases. like query ignores {ȧ} characters

    Actual Data

    ncharTitle
    ———–
    JȧSØÑ

    when running query

    select * from sampletable where ncharTitle like (N’%JȧSØÑ%’)

    select * from sampletable where ncharTitle like (N’%JSØÑ%’)

    returns same result ignoring ‘ȧ’ in 2nd query.

    My collation is Latin1_General_CI_AI

    Any help will be highly appreciated.

    Like

  27. i want to add French word like Catégories in my table with EncryptByKey. Here is my query:

    OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; insert into tbl_Name (Name) values (EncryptByKey( Key_GUID(‘SymmetricKey1′), CONVERT(Nvarchar,’Catégories’)))

    but when i retrieve the value with following query, i got 慃㽴潧楲獥 instead of Catégories

    OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; SELECt CONVERT(NVARCHAR(max),DECRYPTBYKEY(Name)) as Name from tbl_Name

    Expression type varbinary(max) is invalid for COLLATE clause.

    Like

  28. I have name ‘MATT MCANDREW’ in 3 rows under a column in table, when I do a select it it returning 3 rows for that name in upper case, but when I do a DISTINCT column for that name, it is returning the data in proper case(Matt McAndrew). Database and table collation is COLLATE Latin1_General_CI_AS, and if I add COLLATE Case sensitive in distinct statement, it is returning name in uppercase. Could someone please let me know the reason and fix for this, it is happening only for this name alone, and working fine for other names. I really appreciate your help on this. Thanks

    SELECT ARTIST FROM L_PRODUCT

    WHERE ARTIST IN (‘MATT MCANDREW’)

    MATT MCANDREW MATT MCANDREW MATT MCANDREW

    SELECT DISTINCT ARTIST FROM L_PRODUCT

    WHERE ARTIST IN (‘MATT MCANDREW’)

    Matt McAndrew

    SELECT DISTINCT ARTIST COLLATE Latin1_General_CS_AS FROM L_PRODUCT

    WHERE ARTIST IN (‘MATT MCANDREW’)

    MATT MCANDREW

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s