Converting string to hex/binary

[AnStr]

Hello,

I have a 8-digit hex number given as a character column
in my database running under SQL Server 7.0 (not 2000).
I want to convert it into a binary variable in order
to increment it.
I did not succeed with the following statements:

-- Test binary conversion,
-- Vers. 2.x for T-SQL in Query Analyzer -- March 18,
2002
USE MMGUeb
go
SET NOCOUNT ON
declare @strVar1 as varchar(20)
declare @binVar1 as binary(4)
declare @binVar2 as binary(4)
declare @IntVar1 as integer
declare @IntVar2 as integer
-- Example 1
SET @binVar1 = 0xABCD1234
PRINT "Example 1: @binVar1 loaded with hex number in code"
PRINT "SET @binVar1 = 0xabcde123 ----- PRINT @binVar1"
PRINT @binVar1
PRINT "This is not what I wanted to do - "
PRINT " - it simply demonstrates hex numbers"
PRINT "--------------------------------------"
-- Example 2
SET @strVar1 = 'ABCD1234'
SET @binVar1 = cast(@strVar1 as binary)
PRINT "Example 2: Converts the ASCII-Values to hex "
PRINT ".......... not the represented 8-digit hex number"
PRINT "SET @strVar1 = 'ABCD1234' --- SET @binVar1 = cast
(@strVar1 as binary)) --- PRINT @binVar1"
PRINT @binVar1
PRINT "... not the desired result .... "
PRINT "... I wanted to see '0xABCDE1234'"
PRINT "--------------------------------------"
-- Example 3
SET @strVar1 = '0xABCD1234'
SET @binVar1 = cast(@strVar1 as binary)
PRINT "Example 3: Converts the ASCII-Values to hex ... "
PRINT "......... not the represented 8-digit hex number"
PRINT "SET @strVar1 = '0xABCD1234' --- SET @binVar1 =
cast(@strVar1 as binary)) --- PRINT @binVar1"
PRINT @binVar1
PRINT "... not the desired result .... "
PRINT "... I wanted to see '0xABCDE1234'"
PRINT "--------------------------------------"
-- Example 4
SET @IntVar1 = 4*power(16,0) + 3* power(16,1)+ 2* power
(16,2)+ 1* power(16,3) -- 0xABCD1234
SET @IntVar2 = 13*power(16,0) + 12* power(16,1)+ 11* power
(16,2)+ 10* power(16,3) -- 0xABCD1234
SET @binVar1 = cast(@IntVar1 as binary(4))
SET @binVar2 = cast(@IntVar2 as binary(4))
SET @binVar2 = @binVar2 * 256
PRINT "Example 4: Converts integer to hex ... not string
to hex"
PRINT "SET @IntVar1 = 4*power(16,0) +.... --- @binVar1
= cast(@IntVar1 as binary) --- PRINT @binVar1"
PRINT @binVar1
PRINT @binVar2
PRINT "... building hex numbers this way is very
complicated"
PRINT "... no binary arithmetic exceeding 7 hex
digits ... "
PRINT "... no 8-digit hex numbers"
PRINT "... BIGINT datatype is not available in SQL Server
7.0"
PRINT "---------------------------------------------------
----"
go

============== the results are: =========================

Example 1: @binVar1 loaded with hex number in code
SET @binVar1 = 0xabcde123 ----- PRINT @binVar1
0xABCD1234
This is not what I wanted to do -
- it simply demonstrates hex numbers
--------------------------------------
Example 2: Converts the ASCII-Values to hex
.......... not the represented 8-digit hex number
SET @strVar1 = 'ABCD1234' --- SET @binVar1 = cast
(@strVar1 as binary)) --- PRINT @binVar1
0x41424344
... not the desired result ....
... I wanted to see '0xABCDE1234'
--------------------------------------
Example 3: Converts the ASCII-Values to hex ...
......... not the represented 8-digit hex number
SET @strVar1 = '0xABCD1234' --- SET @binVar1 = cast
(@strVar1 as binary)) --- PRINT @binVar1
0x30784142
... not the desired result ....
... I wanted to see '0xABCDE1234'
--------------------------------------
Example 4: Converts integer to hex ... not string to hex
SET @IntVar1 = 4*power(16,0) +.... --- @binVar1 = cast
(@IntVar1 as binary) --- PRINT @binVar1
0x00001234
0x00ABCD00
... building hex numbers this way is very complicated
... no binary arithmetic exceeding 7 hex digits ...
... no 8-digit hex numbers
... BIGINT datatype is not available in SQL Server 7.0
-------------------------------------------------------

Does anyone have a hint for a better solution ?

AnStr

[Steve Kass]

AnStr,

I don't understand what you want. The best I can tell is
that you have a string containing 'ABCD1234' and you
want to do something to it to get the binary value
0xABCDE1234.

Where does the E come from? If it's just a typo, and you
want to turn a string into a varbinary, here's an sp that will
do it:

create procedure char2bin(
@s varchar(30),
@b varbinary(30) output
) as
if @s like '%[^abcdef0123456789]%' begin
set @b = NULL
return
end
declare @sql nvarchar(100)
declare @parms nvarchar(100)
set @sql = N'select @out=0x'+@s
set @parms = N'@out varbinary(30) output'
exec sp_executesql @sql, @parms, @out = @b output
return @b
go

declare @s varchar(30)
set @s = 'ABCD1234'
declare @b varbinary(30)
exec char2bin @s, @b output
select @b
go

drop procedure char2bin

Steve Kass (MVP)
Drew University

AnStr wrote:

> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Name: Converting-to-HEX_Microsoft1.txt
> Converting-to-HEX_Microsoft1.txt Type: Plain Text (text/plain)
> Encoding: quoted-printable

[Umachandar Jayachandran]

Please follow this link for several solutions that you can adapt to your
needs.

http://www.umachandar.com/technical/SQL70Scripts/Main29.htm

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

[AnStr]

Hi,

I have put the hex string to integer conversion
to work, I had asked for. Thank you for the help
supplied.

Today I discovered that I need the inverse function, too.
I.e. a conversion from integer or binary
to a string representing the hex numbers of
the binary.

Unhappily "convert(char, @VarBin)" treats my
@VarBin as a representation of printable characters
and tries to "print" them to the output.
E.g. @VarBin = 0x3132 converts to "21" but not
to the String "3132".

The only T-SQL command I know to do the correct
conversion is "PRINT" - but
" Exec sp_ExecuteSql N'PRINT @VarBin' ,
N'@HexStr2 char Out', @HexStr2 Out "
is to crude a construction to work.

A solution using a conversion table, similar to
that suggested by Umachandar Jayachandran,
(www.umachandar.com/technical/) seems to be
possible but somewhat intricate.

Can anyone recommend a short solution for a
integer or binary to string conversion
giving a 8-digit hex number in the output ?

Regards
AnStr
------------------------------------------------------

Hi Umachandar,

thanks for your hints. I have tried your third
example for my hex string to integer conversion
and it works fine - even with 8-digit hex numbers.

Regards
AnStr

>-----Original Message-----
> Please follow this link for several solutions that

> you can adapt to your eeds.
>
>http://www.umachandar.com/technical/SQL70Scripts/Main29.ht
m
>Umachandar Jayachandran

[Steve Kass]

AnStr,

Here's one way to do it:

create procedure hexchar(
@i int,
@s varchar(8) output
) as
if @i < 0 begin
set @s = null
return
end
declare @digits char(16)
set @digits = '0123456789ABCDEF'
set @s = cast(substring(@digits,@i%16+1,1) as char(1))
set @i = @i/16
while @i > 0 begin
set @s = cast(substring(@digits,@i%16+1,1) as char(1))+@s
set @i = @i/16
end
if len(@s)%2 = 1 set @s = '0'+@s
go

declare @s varchar(8)

exec hexchar 65536, @s output
select 65536, @s

exec hexchar 0, @s output
select 0, @s

exec hexchar -1, @s output
select -1, @s

exec hexchar 2140000000, @s output
select 2140000000, @s

exec hexchar 0xabcdef, @s output
select 0xabcdef, @s
go

drop procedure hexchar

Steve