Professional Web Applications Themes

Compare string case sensitive - Microsoft SQL / MS SQL Server

How to compare 2 strings (or fields) in case sensitive manner? I'd like to do it without changing the configuration of whole server. Regards Miroo...

  1. #1

    Default Compare string case sensitive

    How to compare 2 strings (or fields) in case sensitive manner?
    I'd like to do it without changing the configuration of whole server.

    Regards
    Miroo
    Miroo_news Guest

  2. #2

    Default Re: Compare string case sensitive



    "Miroo_news" <poczta.fm> wrote in message
    news:bf5mm7$okd$news.tpi.pl... 


    Hello Miroo,

    When you compare two varchars you want to compare them as a varbinary
    values. That way you'll get case sensitive comparation. For example:

    DECLARE stringa varchar(20), stringb varchar(20)

    SET stringa = 'a'
    SET stringb = 'A'


    IF CAST(stringa as varbinary)= CAST(stringb as varbinary)
    PRINT 'same'
    ELSE
    PRINT 'different'

    --
    Regards,

    Tomislav Kralj
    tel.hr


    Tomislav Guest

  3. #3

    Default Re: Compare string case sensitive

    Use COLLATE to make them case sensitive:

    SELECT ...
    WHERE firstname COLLATE Latin1_General_CS_AS = employees.firstname COLLATE
    Latin1_General_CS_AS

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Miroo_news" <poczta.fm> wrote in message
    news:bf5mm7$okd$news.tpi.pl... 


    Jacco Guest

  4. #4

    Default Re: Compare string case sensitive

    If you are using Sql2K then you can use

    select * from <tableName>
    where BINARY_CHECKSUM(Column1)= BINARY_CHECKSUM(Column2)

    HTH

    Praveen Maddali,
    MCSD, MCDBA

    "Miroo_news" <poczta.fm> wrote in message
    news:bf5mm7$okd$news.tpi.pl... 


    praveen Guest

  5. #5

    Default Re: Compare string case sensitive

    Użytkownik "Miroo_news" <poczta.fm> napisał w wiadomo¶ci
    news:bf5mm7$okd$news.tpi.pl... 

    Thank you guys,
    As I can see there is a lot of ways to do it, but
    I have still problems with null values.
    I need this in a trigger to change date of modification
    of a record. Fields can be different types and can be nullable.

    So I'm interested in such query:
    exists (select 1 where inserted.field <> deleted.field)

    Using BINARY_CHECKSUM (I think it's the fastest way)
    I can't compare NULL to NOT NULL. If I use
    ISNULL(field,0) I can't find field which changed
    from null to 0 and vice versa.

    The same using CAST AS VARBINARY. When I do
    ISNULL(CAST(field AS VARBINARY),0x) both empty strings
    and nulls will hace the same value.

    So I think I will use VARBINARY because more important
    to me is change from null to 0 then null to empty string.

    I know I'm quite a lamer, maybe you know a solution?

    I didn't tested a COLLATION method yet, I'm not sure
    if it can be used with non-string fields and nulls.

    Best regards
    Miroo

    Miroo_news Guest

  6. #6

    Default Re: Compare string case sensitive

    Check the information about IF UPDATE and IF (COLUMNS_UPDATED)
    under CREATE TRIGGER in Books Online, that might be a far easier solution to
    achieve what you want.

    COLLATE works on character type (char, nchar, varcahr, nvarchar, text,
    ntext) variables and columns, whether they are NULL or not. It doesn't work
    with any other datatypes.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Miroo_news" <poczta.fm> wrote in message
    news:bf5s73$f51$news.tpi.pl... 
    >
    > Thank you guys,
    > As I can see there is a lot of ways to do it, but
    > I have still problems with null values.
    > I need this in a trigger to change date of modification
    > of a record. Fields can be different types and can be nullable.
    >
    > So I'm interested in such query:
    > exists (select 1 where inserted.field <> deleted.field)
    >
    > Using BINARY_CHECKSUM (I think it's the fastest way)
    > I can't compare NULL to NOT NULL. If I use
    > ISNULL(field,0) I can't find field which changed
    > from null to 0 and vice versa.
    >
    > The same using CAST AS VARBINARY. When I do
    > ISNULL(CAST(field AS VARBINARY),0x) both empty strings
    > and nulls will hace the same value.
    >
    > So I think I will use VARBINARY because more important
    > to me is change from null to 0 then null to empty string.
    >
    > I know I'm quite a lamer, maybe you know a solution?
    >
    > I didn't tested a COLLATION method yet, I'm not sure
    > if it can be used with non-string fields and nulls.
    >
    > Best regards
    > Miroo
    >[/ref]


    Jacco Guest

  7. #7

    Default Re: Compare string case sensitive

    Miroo,

    The main use of BINARY_CHECKSUM is to detect changes to a row of a table.
    You can always compare NULL to NOT NULL using BINARY_CHECKSUM .You can also
    use BINARY_CHECKSUM(*) to compare cahnges in all the columns of a row using
    a single expression. Check BOL for more detailed explanation.

    Have you checked the reults by using BINARY_CHECKSUM?

    Praveen Maddali,
    MCSD, MCDBA



    "Miroo_news" <poczta.fm> wrote in message
    news:bf5s73$f51$news.tpi.pl... 
    >
    > Thank you guys,
    > As I can see there is a lot of ways to do it, but
    > I have still problems with null values.
    > I need this in a trigger to change date of modification
    > of a record. Fields can be different types and can be nullable.
    >
    > So I'm interested in such query:
    > exists (select 1 where inserted.field <> deleted.field)
    >
    > Using BINARY_CHECKSUM (I think it's the fastest way)
    > I can't compare NULL to NOT NULL. If I use
    > ISNULL(field,0) I can't find field which changed
    > from null to 0 and vice versa.
    >
    > The same using CAST AS VARBINARY. When I do
    > ISNULL(CAST(field AS VARBINARY),0x) both empty strings
    > and nulls will hace the same value.
    >
    > So I think I will use VARBINARY because more important
    > to me is change from null to 0 then null to empty string.
    >
    > I know I'm quite a lamer, maybe you know a solution?
    >
    > I didn't tested a COLLATION method yet, I'm not sure
    > if it can be used with non-string fields and nulls.
    >
    > Best regards
    > Miroo
    >[/ref]


    praveen Guest

  8. #8

    Default Re: Compare string case sensitive


    Użytkownik "Jacco Schalkwijk" <co.uk> napisał w
    wiadomości news:phx.gbl... 
    to 

    IF UPDATE is true *always* when you set
    a value to a field, doesn't matter if it's
    different from previous value.

    It's a problem, because I always update all
    fields, but change some of them or none of them,
    so I can't use it.

    Regards
    Miroo

    Miroo_news Guest

  9. #9

    Default Re: Compare string case sensitive


    Użytkownik "praveen" <stph.net> napisał w wiadomości
    news:phx.gbl... 
    also 
    using 

    Oops, my mistake, sorry.
    You know, I'm a *real* :) programmer so I do things
    and when they fail only then I read a manual ;)
    But I'll be better and i will read manuals.
    I promise.

    I've checked an expression:
    select BINARY_CHECKSUM(NULL)
    and it resulted with an error
    "Error expanding '*': all columns incomparable,
    '*' expanded to zero columns."

    When I checked with a field or a variable
    everything was OK. I think I'll love it.
    Thank you.

    Regards
    Miroo

    Miroo_news Guest

Similar Threads

  1. SQL 92 - case sensitive
    By rabaaoui abdelhak in forum MySQL
    Replies: 1
    Last Post: February 28th, 05:49 PM
  2. #23026 [Com]: Make Zend case-sensitive (classes, functions, remove case-insensitive)
    By nvivo at mandic dot com dot br in forum PHP Development
    Replies: 0
    Last Post: October 19th, 12:17 PM
  3. Case Sensitive
    By amit in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 31st, 12:32 PM
  4. Glob() is Case Sensitive
    By Krhis in forum PHP Development
    Replies: 1
    Last Post: July 26th, 04:30 AM
  5. getPos is case sensitive
    By Lingo Dude in forum Macromedia Director Lingo
    Replies: 9
    Last Post: July 17th, 05:29 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139