SQL Server Performance

nvarchar to varbinary conversion: different strings but still matching

Discussion in 'ALL SQL SERVER QUESTIONS' started by bhatnagarp, Jul 20, 2012.

  1. bhatnagarp New Member

    nvarchar to varbinary conversion: different strings but still matching

    Declare @Token1 nvarchar(255) = '!ˢdžɼĀěɀ?ǭöıǽÀʸÕƣŬVLJ˯ŤLʝ˨˜ƛÀX0ě0ɍ',
    @Token2 nvarchar(255) = '!ˢdžɼĀěɀ?ǭöıǽÀʸÕƣŬVLJ˯ŤLʝ˨˜ƛÀX0ě0ʭ',
    @TokenBin1 varbinary(2000),
    @TokenBin2 varbinary(2000)

    Set @TokenBin1 = CONVERT(varbinary(2000), @Token1)
    Set @TokenBin2 = CONVERT(varbinary(2000), @Token2)

    Select @TokenBin1, @TokenBin2

    IF @TokenBin1 = @TokenBin2
    Select 1
    else
    Select 0

    Both the strings are different (last character) but are still matching.
  2. Shehap MVP, MCTS, MCITP SQL Server

    It doesn't depend here on the exact equivalence between nvarchar variables in letters or but it depends on the equivalence of their translated ASCII characters like out case here 'ɍ' and 'ʭ' and as long as conversion to varbinary
    will be from ASCII characters , then they should have identical varbinary values evidenced much more by the below T-SQL queries :

    Declare@Token1nvarchar(max)='ɍ',
    @Token2nvarchar(max)='ʭ',
    @TokenBin1varbinary(max),
    @TokenBin2varbinary(max)

    Set@TokenBin1=CONVERT(varbinary(max),@Token1)
    Set@TokenBin2=CONVERT(varbinary(max),@Token2)

    Select@TokenBin1
    select@TokenBin2

    IF@TokenBin1=@TokenBin2
    Select 1 asequivalent

    else
    Select 0 asequivalent

    Whereas it can't happen in other cases as follows:

    Declare@Token1nvarchar(max)='G',
    @Token2nvarchar(max)='F',
    @TokenBin1varbinary(max),
    @TokenBin2varbinary(max)

    Set@TokenBin1=CONVERT(varbinary(max),@Token1)
    Set@TokenBin2=CONVERT(varbinary(max),@Token2)

    Select@TokenBin1
    select@TokenBin2

    IF@TokenBin1=@TokenBin2
    Select 1 asequivalent

    else
    Select 0 asequivalent

    Kindly let me know if any further help is needed

Share This Page