convert hex string to hex literal

12 Jan 2006 5:56 AM

JTL

how can i tell sql server to execute the "convert" function using the
literal hex value, 0xC0, rather than the string value '0xC0'?

for example, this works:
declare @myhexint as int
set @myhexint = 0xC0
select convert(int, @myhexint)

but this does not:
declare @myvarchar as varchar(20)
set @myvarchar = '0xC0'
select convert(int, @myvarchar)

i need to somehow convert a varchar representation of a hex string, to a
literal hex value..

tia,

jt

12 Jan 2006 7:04 AM

Jeff A. Stucker (MVP)

I have copied an old response to this same question, below my signature
line.

--
Cheers,

'('     Jeff A. Stucker
\
        Senior Consultant
        www.rapidigm.com

            Itzik Ben-Gan
     Oct 29 2002, 11:44 am   show options

      Newsgroups: microsoft.public.sqlserver.programming

From: "Itzik Ben-Gan" <itz***@hi-tech.co.il> -

      Date: Tue, 29 Oct 2002 17:27:43 +0200
      Local: Tues, Oct 29 2002 7:27 am
      Subject: Re: Convert hex (stored as string) to string?

If you're using SQL Server 2000, you can use the following function:

CREATE FUNCTION dbo.fn_chartobin
(
@hexstr AS varchar(8000)
)
RETURNS varbinary(4000)
AS
BEGIN

IF @hexstr IS NULL RETURN NULL

DECLARE
    @curbyte AS int,
    @varbin AS varbinary(4000)

IF @hexstr LIKE '0x%' SET @hexstr = SUBSTRING(@hexstr, 3, 8000)

SET @hexstr =
    CASE LEN(@hexstr) % 2 WHEN 1 THEN '0' ELSE '' END + @hexstr

SET @varbin = 0x
SET @curbyte = LEN(@hexstr) / 2

WHILE @curbyte > 0
BEGIN
    SET @varbin =
      CAST(
        CASE SUBSTRING(@hexstr, @curbyte * 2, 1)
          WHEN '0' THEN 0x00
          WHEN '1' THEN 0x01
          WHEN '2' THEN 0x02
          WHEN '3' THEN 0x03
          WHEN '4' THEN 0x04
          WHEN '5' THEN 0x05
          WHEN '6' THEN 0x06
          WHEN '7' THEN 0x07
          WHEN '8' THEN 0x08
          WHEN '9' THEN 0x09
          WHEN 'A' THEN 0x0A
          WHEN 'B' THEN 0x0B
          WHEN 'C' THEN 0x0C
          WHEN 'D' THEN 0x0D
          WHEN 'E' THEN 0x0E
          WHEN 'F' THEN 0x0F
        END |
        CAST(
          CASE SUBSTRING(@hexstr, @curbyte * 2 - 1, 1)
            WHEN '0' THEN 0x00
            WHEN '1' THEN 0x10
            WHEN '2' THEN 0x20
            WHEN '3' THEN 0x30
            WHEN '4' THEN 0x40
            WHEN '5' THEN 0x50
            WHEN '6' THEN 0x60
            WHEN '7' THEN 0x70
            WHEN '8' THEN 0x80
            WHEN '9' THEN 0x90
            WHEN 'A' THEN 0xA0
            WHEN 'B' THEN 0xB0
            WHEN 'C' THEN 0xC0
            WHEN 'D' THEN 0xD0
            WHEN 'E' THEN 0xE0
            WHEN 'F' THEN 0xF0
          END AS tinyint) AS binary(1))
      + @varbin
    SET @curbyte = @curbyte - 1
END

RETURN @varbin

END
GO

-- test
SELECT dbo.fn_chartobin('0x2056697361205061796D656E747878787800')

0x2056697361205061796D656E747878787800

--
BG
SQL Server MVP

12 Jan 2006 8:48 AM

JTL

awesome! just what i needed- thanks a million..

jt

Show quote

"Jeff A. Stucker (MVP)" <j***@mobilize.net> wrote in message
news:utznjZ0FGHA.1424@TK2MSFTNGP12.phx.gbl...
>I have copied an old response to this same question, below my signature
>line.
>
> --
> Cheers,
>
> '('     Jeff A. Stucker
> \
>        Senior Consultant
>        www.rapidigm.com
>
>            Itzik Ben-Gan
>     Oct 29 2002, 11:44 am   show options
>
>      Newsgroups: microsoft.public.sqlserver.programming
>      From: "Itzik Ben-Gan" <itz***@hi-tech.co.il> -
>      Date: Tue, 29 Oct 2002 17:27:43 +0200
>      Local: Tues, Oct 29 2002 7:27 am
>      Subject: Re: Convert hex (stored as string) to string?
>
>
> If you're using SQL Server 2000, you can use the following function:
>
>
> CREATE FUNCTION dbo.fn_chartobin
> (
> @hexstr AS varchar(8000)
> )
> RETURNS varbinary(4000)
> AS
> BEGIN
>
>
> IF @hexstr IS NULL RETURN NULL
>
>
> DECLARE
>    @curbyte AS int,
>    @varbin AS varbinary(4000)
>
>
> IF @hexstr LIKE '0x%' SET @hexstr = SUBSTRING(@hexstr, 3, 8000)
>
>
> SET @hexstr =
>    CASE LEN(@hexstr) % 2 WHEN 1 THEN '0' ELSE '' END + @hexstr
>
>
> SET @varbin = 0x
> SET @curbyte = LEN(@hexstr) / 2
>
>
> WHILE @curbyte > 0
> BEGIN
>    SET @varbin =
>      CAST(
>        CASE SUBSTRING(@hexstr, @curbyte * 2, 1)
>          WHEN '0' THEN 0x00
>          WHEN '1' THEN 0x01
>          WHEN '2' THEN 0x02
>          WHEN '3' THEN 0x03
>          WHEN '4' THEN 0x04
>          WHEN '5' THEN 0x05
>          WHEN '6' THEN 0x06
>          WHEN '7' THEN 0x07
>          WHEN '8' THEN 0x08
>          WHEN '9' THEN 0x09
>          WHEN 'A' THEN 0x0A
>          WHEN 'B' THEN 0x0B
>          WHEN 'C' THEN 0x0C
>          WHEN 'D' THEN 0x0D
>          WHEN 'E' THEN 0x0E
>          WHEN 'F' THEN 0x0F
>        END |
>        CAST(
>          CASE SUBSTRING(@hexstr, @curbyte * 2 - 1, 1)
>            WHEN '0' THEN 0x00
>            WHEN '1' THEN 0x10
>            WHEN '2' THEN 0x20
>            WHEN '3' THEN 0x30
>            WHEN '4' THEN 0x40
>            WHEN '5' THEN 0x50
>            WHEN '6' THEN 0x60
>            WHEN '7' THEN 0x70
>            WHEN '8' THEN 0x80
>            WHEN '9' THEN 0x90
>            WHEN 'A' THEN 0xA0
>            WHEN 'B' THEN 0xB0
>            WHEN 'C' THEN 0xC0
>            WHEN 'D' THEN 0xD0
>            WHEN 'E' THEN 0xE0
>            WHEN 'F' THEN 0xF0
>          END AS tinyint) AS binary(1))
>      + @varbin
>    SET @curbyte = @curbyte - 1
> END
>
>
> RETURN @varbin
>
>
> END
> GO
>
>
> -- test
> SELECT dbo.fn_chartobin('0x2056697361205061796D656E747878787800')
>
>
> 0x2056697361205061796D656E747878787800
>
>
> --
> BG
> SQL Server MVP
>
>

12 Jan 2006 10:21 AM

Rogas69

not longer ago than yesterday i needed something similar..
there is a nice technique here
http://milambda.blogspot.com/2005/08/when-binary-values-come-as-characters.html -
thanks ML

Peter