Post reply

Text Matching with Case sensitive

  • June 8, 2011 at 1:44 am

    #389685

    Hi SQL-Lovers

    Dear Friends i have a database with Case InSensitive

    but as for the query i want to check the text with case sensitivity

    Assume

    value stored in column as 'India' , now i want to search text for india with case sensitive

    I want result in below format

    'India' = 'India' - Correct

    'India' = 'INDIA' - Wrong

    Is there any thing in SQL Server where i can check text in where condition with case sensitive without changing the case sentitivity at database level.

    Syed Sami Ur Rehman
    SQL-DBA
    Creative Technosoft System | www.cts-in.com

  • June 8, 2011 at 6:04 am

    #1335568

    yes...all you have to do is COLLATE using a case sensitive collation just for the comparison/where statement:

    ...where name <> upper(name) collate Latin1_General_BIN

    SELECT

    CASE

    WHEN 'India' = 'INDIA' collate Latin1_General_BIN

    THEN 'Same'

    ELSE 'different'

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 8, 2011 at 6:22 am

    #1335580

    Thanks very much Lowell

    This is what i am looking for.:-P

    Just one final question can i have different collate type or should always go with Latin1_General1_BIN

    Syed Sami Ur Rehman
    SQL-DBA
    Creative Technosoft System | www.cts-in.com

  • June 8, 2011 at 6:32 am

    #1335590

    as far as i know, you could use a case sensitive specific, but all the examples i've tripped on here in the forums always go compare binary; some collations cannot convert to other collations, but it seems like they can all convert to binary.

    here's a perfect example: this fails with an error:

    SELECT

    CASE

    WHEN 'India' COLLATE Latin1_General_CI_AS = 'INDIA' collate SQL_Latin1_General_Cp1_CS_AS

    THEN 'Same'

    ELSE 'different'

    END

    /*

    Msg 468, Level 16, State 9, Line 3

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_CI_AS" in the equal to operation.

    */

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 8, 2011 at 7:01 am

    #1335620

    Really very much thanks.:-)

    Syed Sami Ur Rehman
    SQL-DBA
    Creative Technosoft System | www.cts-in.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply