hex to bin puzzle

Hello sql gurus, I have a puzzle for you...
I have a string that happens to be a series of hex digits that I need to put
"as is" into a binary or varbinary field, but when I use cast or convert, it
is actually translating each character in the string to a binary hex values
like this
select cast( '0x24806ff96b02a3a1f16cb2840598b236' as binary)
returns
0x30783234383036666639366230326133613166
3136636232383430353938
this works
select cast( 0x24806ff96b02a3a1f16cb2840598b236 as binary)
returns
0x24806ff96b02a3a1f16cb2840598b236
this doesn't work as needed
declare @.t varchar(32)
set @.t = 24806ff96b02a3a1f16cb2840598b236
select cast( @.t as binary)
returns
0x30783234383036666639366230326133613166
3136636232383430353938
what i want is just that value as is in binary... I know if I take the
quotes off the hex string everything will work just fine, the problem is I
want to use a varchar parameter in a function or sp and SQL is treating that
as a string and doing it the "wrong" way... any ideas'?
Thanks,
Scotthi
when i tried
declare @.t varchar(32)
set @.t = 0x24806ff96b02a3a1f16cb2840598b236
select cast( @.t as binary)
it worked
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Scott Riehl" wrote:

> Hello sql gurus, I have a puzzle for you...
> I have a string that happens to be a series of hex digits that I need to p
ut
> "as is" into a binary or varbinary field, but when I use cast or convert,
it
> is actually translating each character in the string to a binary hex value
s
> like this
> select cast( '0x24806ff96b02a3a1f16cb2840598b236' as binary)
> returns
> 0x30783234383036666639366230326133613166
3136636232383430353938
> this works
> select cast( 0x24806ff96b02a3a1f16cb2840598b236 as binary)
> returns
> 0x24806ff96b02a3a1f16cb2840598b236
> this doesn't work as needed
> declare @.t varchar(32)
> set @.t = 24806ff96b02a3a1f16cb2840598b236
> select cast( @.t as binary)
> returns
> 0x30783234383036666639366230326133613166
3136636232383430353938
> what i want is just that value as is in binary... I know if I take the
> quotes off the hex string everything will work just fine, the problem is I
> want to use a varchar parameter in a function or sp and SQL is treating th
at
> as a string and doing it the "wrong" way... any ideas'?
> Thanks,
> Scott
>
>|||Thanks, but try putting quotes around it and it fails...which is the way
SQL is treating the values I am pulling them from a table...or passing it
as a parameter...
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:B26891C8-23CA-41DC-A435-A6BB984F4286@.microsoft.com...
> hi
> when i tried
> declare @.t varchar(32)
> set @.t = 0x24806ff96b02a3a1f16cb2840598b236
> select cast( @.t as binary)
> it worked
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Scott Riehl" wrote:
>|||then, try putting the value directly without converting it
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Scott Riehl" wrote:

> Thanks, but try putting quotes around it and it fails...which is the way
> SQL is treating the values I am pulling them from a table...or passing it
> as a parameter...
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:B26891C8-23CA-41DC-A435-A6BB984F4286@.microsoft.com...
>
>|||Scott,
See if this helps you out. The script here may also be adaptable:
http://www.umachandar.com/technical...ipts/Main29.htm
create function hexString2Bin(
@.s varchar(8000)
) returns varbinary(8000) as begin
if @.s like '0x%' or @.s like '0X%'
set @.s = substring(@.s,3,8000)
if @.s like '%[^abcdefABCDEF0123456789]%'
or len(@.s) % 2 = 1 return NULL
declare @.result varbinary(8000)
declare @.pos int
set @.pos = len(@.s)
set @.result = 0x
while @.pos > 0 begin
set @.result = cast(
charindex(substring(@.s,@.pos,1),'01234567
89ABCDEF')-1
+16*(charindex(substring(@.s,@.pos-1,1),'0123456789ABCDEF')-1)
as binary(1)) + @.result
set @.pos = @.pos - 2
end
return @.result
end
go
select 'ABCD1234', dbo.hexString2Bin('ABCD1234')
select '2ABCD1234', dbo.hexString2Bin('2ABCD1234')
select '0x00ABCD1234', dbo.hexString2Bin('0x00ABCD1234')
go
drop function hexString2Bin
-- Steve Kass
-- Drew University
-- D4A34D6E-58E7-4400-9504-EA255921AE9F
Scott Riehl wrote:

>Hello sql gurus, I have a puzzle for you...
>I have a string that happens to be a series of hex digits that I need to pu
t
>"as is" into a binary or varbinary field, but when I use cast or convert, i
t
>is actually translating each character in the string to a binary hex values
>like this
>select cast( '0x24806ff96b02a3a1f16cb2840598b236' as binary)
>returns
> 0x30783234383036666639366230326133613166
3136636232383430353938
>this works
>select cast( 0x24806ff96b02a3a1f16cb2840598b236 as binary)
>returns
>0x24806ff96b02a3a1f16cb2840598b236
>this doesn't work as needed
>declare @.t varchar(32)
>set @.t = 24806ff96b02a3a1f16cb2840598b236
>select cast( @.t as binary)
>returns
> 0x30783234383036666639366230326133613166
3136636232383430353938
>what i want is just that value as is in binary... I know if I take the
>quotes off the hex string everything will work just fine, the problem is I
>want to use a varchar parameter in a function or sp and SQL is treating tha
t
>as a string and doing it the "wrong" way... any ideas'?
>Thanks,
>Scott
>
>
>|||Of course this only works because the binary value supplied to your varchar
variable is implicitly converted to varchar.
ML|||And to think I thought this procedure may never ever be of any real use...
[url]http://milambda.blogspot.com/2005/08/when-binary-values-come-as-characters.html[/u
rl]
Have fun. I hope you don't mind me taking your binary value as an example of
use.
ML