Transact SQL – Conversion and Binary Data Type – Day 01

Background

Trying to convert an Hexadecimal number to Decimal, but ran into stumbling issues.

Referenced Source

  1. StackOverflow
    • MS SQL server – convert HEX string to integer
      Link

Convert to Binary and then to Int

Get Number, Length, and Length in Pairs

Code


declare @data varchar(100)
declare @dataLen int
declare @dataLenPair int

set @data = '0x89'

set @dataLen = len(@data) -2
set @dataLenPair =  (len(@data) -2)/2

Output

convertToBinary.01.20190302.1005AM

Convert To Int Using Convert Function

Code


declare @data varchar(100)
declare @dataLen int
declare @dataLenPair int

set @data = '0x89'

set @dataLen = len(@data) -2
set @dataLenPair =  (len(@data) -2)/2

select
      [@data] = @data
    , [@dataLen] = @dataLen
    , [@dataLenPair] = @dataLenPair

/*
	Convert To Int using Using Convert
*/
select
      [@data] = @data
    , [varbin] = CONVERT(INT, CONVERT(VARBINARY, @data) )
    , [varbin1] = CONVERT(INT, CONVERT(VARBINARY(1), @data) )

Output

convertToBinary.02.20190302.1010AM

Explanation

  1. The convert operator that uses varbinary, comes back with a high decimal number
  2. The one that uses varbinary(1) is much a smaller number

Convert To Int Using Cast Function

Code


declare @data varchar(100)
declare @dataLen int
declare @dataLenPair int

set @data = '0x89'

set @dataLen = len(@data) -2
set @dataLenPair =  (len(@data) -2)/2

select
      [@data] = @data
    , [@dataLen] = @dataLen
    , [@dataLenPair] = @dataLenPair

/*
	Convert to Int using Cast
*/
select
       [@data] = @data
     , [varbin] = CONVERT(INT, CAST(@data AS VARBINARY))
     , [varbin1] = CONVERT(INT, CAST(@data AS VARBINARY(1)))

Output

convertToBinary.03.20190302.1020AM

Explanation

  1. The cast operator that uses varbinary, comes back with a high decimal number
  2. The one that uses varbinary(1) is much a smaller number

Convert To Varbinary Using Cast Function

Code


declare @data varchar(100)
declare @dataLen int
declare @dataLenPair int

set @data = '0x89'

set @dataLen = len(@data) -2
set @dataLenPair =  (len(@data) -2)/2

select
          [@data] = @data
        , [@dataLen] = @dataLen
        , [@dataLenPair] = @dataLenPair

select
          [@data] = @data
        , [@varbin] = CAST(@data AS VARBINARY)
        , [@varbin1] = CAST(@data AS VARBINARY(1))

 

Output

convertToBinary.04.20190302.1024AM

Explanation

  1. The cast operator that uses varbinary, comes back with a high decimal number
  2. The one that uses varbinary(1) is much a smaller number

Conclusion

Please be very careful with the binary datatype when using it in the course of convert or cast function.

Couple of things to look for :-

  1. The size of the binary datatype has to be specified
  2. And, it needs to be hard-coded, as a variable can not be used

Dedicated

Dedicated to Stack Overflow and its members.

Noted members :-

  1. Pondlife

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google 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 )

Connecting to %s