Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    33

    Arrow Unanswered: Case Sensitive VarChar comparsion??????

    Hi all,

    I wonder if it is possible to use case sensitive varchar comparsion in a query?
    As I have tried and the varchar comparsion is non-case sensitive.

    Thanks
    ME

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    if (convert (varbinary,'Enigma')= convert (varbinary,'enigma'))
    Print 'Done'
    else
    Print 'Not Done'


    if (convert (varbinary,'Enigma')= convert (varbinary,'Enigma'))
    Print 'Done'
    else
    Print 'Not Done'
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For a case-sensitive compare, I'd use:
    Code:
    IF 'PatP' = 'patp' COLLATE SQL_Latin1_General_CP1250_CS_AS
       PRINT 'Match'
    ELSE
       PRINT 'Different'
    You can get a list of collations that a given server will support using:
    Code:
    SELECT *
       FROM ::fn_helpcollations()
    -PatP

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Pat ... any reason you would not recommend a cast to varbinary
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Two reasons.

    First of all, if you want a string compare, I think you should do a string compare... There are lots of ways to compare strings, and keeping that flexibility is important to me. Today the users want case sensitivity, tomorrow they'll want the strings ordered using the Spanish dictionary, which is easy using a collation.

    Second, casting to BINARY has a cost, and in large jobs that cost can get high. Why incur overhead that gives no real benefit?

    -PatP

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Thanks ....
    Learned something new again ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •