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

109 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! :)

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

  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.

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

  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…

  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

  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?

  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?

  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

  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.

  12. Thank you. Time and again I have found your website and blogs very valuable and helpful. I appreciate your efforts.

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

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

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

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

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

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

    Md. Sumonur Rahman
    Dot Net Programmer
    Bangladesh

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

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

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

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

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

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

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

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

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

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

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