convert hex string to hex literal

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,
jtI 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('0x2056697361205061796D656E
747878787800')
0x2056697361205061796D656E747878787800
BG
SQL Server MVP|||awesome! just what i needed- thanks a million..
jt
"Jeff A. Stucker (MVP)" <jeff@.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('0x2056697361205061796D656E
747878787800')
>
> 0x2056697361205061796D656E747878787800
>
> --
> BG
> SQL Server MVP
>|||not longer ago than yesterday i needed something similar..
there is a nice technique here
[url]http://milambda.blogspot.com/2005/08/when-binary-values-come-as-characters.html[/u
rl] -
thanks ML
Peter