Convert hex into char/ http - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert hex into char/ http

Author

Topic

sara_baboo
Starting Member

8 Posts

Posted - 2004-07-07 : 22:55:51

1) Can somebody tell me how to convert hex data to the original character
example the @data = '0054'. after convert the answer is 'T'
can varchar converted to varbinary?

2) how to use http in transact sql to read the data from gateway to sqlserver. pls help me on this

3) how to allocate the return value of EXEC sp_executesql @tyt into
a normal variable that created as
declare @con varchar(100)

tq

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-07 : 23:31:29

1> Use the CHAR function with leading 0x:
SELECT char(0x0054)
Returns 'T'

2> Check out BOL under "Retrieving XML Documents Using FOR XML"

sara_baboo
Starting Member

8 Posts

Posted - 2004-07-08 : 00:18:53

quote:
Originally posted by timmy

1> Use the CHAR function with leading 0x:
SELECT char(0x0054)
Returns 'T'

2> Check out BOL under "Retrieving XML Documents Using FOR XML"

Thank you for your reply ..
but i have small problem..
actually i will receive a content of hex data to the program

@data = '005400420045'
how should i declare @data (varchar or varbinary?)
and then how can i convert it to 0x____
in order to find the char?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-08 : 01:10:48

This is messy, but does the trick.... however it does limit what it can be applied to.

declare @hex varchar(40),
@n int,
@len int,
@str nvarchar(100)

set @hex = '005400420045'
set @n = 0
set @len = len(@hex)/4
set @str = N'SELECT '

WHILE @n < @len
BEGIN
print Substring(@hex, @n * 4 + 1, 4)

SET @str = @str + N'Char(0x' + Substring(@hex, @n * 4 + 1, 4) + ') + '
print @str
SET @n = @n + 1
END

set @str = left(@str, len(@str)-2)

exec sp_executesql @str

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-08 : 02:50:33

This should be a very fast solution. It works like Timmy's but uses a Numbers table.


declare @hex varchar(40) 
set @hex = '005400420045'
DECLARE @sql varchar(8000)
SELECT @sql = COALESCE(@sql + '),'''')+ ', 'SELECT ') + 'IsNull(char(0x' + SubString(@hex, id*4 - 3, 4) FROM Numbers WHERE id*4 <= Len(@hex)
select (@sql + '),'''')')
exec (@sql + '),'''')')

---- Outputs
SELECT IsNull(char(0x0054),'')+ IsNull(char(0x0042),'')+ IsNull(char(0x0045),'')
---- 
TBE

.
.
.

You need a table with numbers in it. It comes in handy for many things. Make one if you don't have one:


SELECT TOP 8000 ID = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3

If you are absolutely opposed to making a permanent table, you can do this, but why?


declare @hex varchar(40) 
set @hex = '005400420045'
DECLARE @sql varchar(8000)
SELECT @sql = COALESCE(@sql + '),'''')+ ', 'SELECT ') + 'IsNull(char(0x' + SubString(@hex, id*4 - 3, 4) FROM 
(
 select (a0.id + a1.id + a2.id + a3.id ) id FROM 
(select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) a0,  
(select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union  select 90) a1,                 
(select 0 id union select 100 union select 200 union select 300 union select 400 union select 500 union select 600 union select 700 union select 800 union select 900) a2,
(select 0 id union select 1000 union select 2000) a3
) Numbers WHERE id*4 <= Len(@hex) and id > 0
select (@sql + '),'''')')
exec (@sql + '),'''')')

Or, to make it more readable without making a permanent table,

Create view ZeroToTen AS select 0 ID union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union select 8 union select 9


declare @hex varchar(40) 
set @hex = '005400420045'
DECLARE @sql varchar(8000)
SELECT @sql = COALESCE(@sql + '),'''')+ ', 'SELECT ') + 'IsNull(char(0x' + SubString(@hex, id*4 - 3, 4) FROM 
(
Select T1.id + T2.id * 10 + T3.id * 100 + T4.id * 1000 id
from ZeroToTen T1, ZeroToTen T2, ZeroToTen T3, ZeroToTen T4
) Numbers WHERE id*4 <= Len(@hex) and id > 0
select (@sql + '),'''')')
exec (@sql + '),'''')')

sara_baboo
Starting Member

8 Posts

Posted - 2004-07-08 : 04:45:17

quote:
Originally posted by timmy

This is messy, but does the trick.... however it does limit what it can be applied to.

declare @hex varchar(40),
@n int,
@len int,
@str nvarchar(100)

set @hex = '005400420045'
set @n = 0
set @len = len(@hex)/4
set @str = N'SELECT '

WHILE @n < @len
BEGIN
print Substring(@hex, @n * 4 + 1, 4)

SET @str = @str + N'Char(0x' + Substring(@hex, @n * 4 + 1, 4) + ') + '
print @str
SET @n = @n + 1
END

set @str = left(@str, len(@str)-2)

exec sp_executesql @str

hi timmy,
thank it does work..

exec sp_executesql @str
does display the string but i want it to go back into another
variable as i need to reuse the variable.
how to pass the output of --exec sp_executesql @str into another variable.

about http..i couldn't find...
can anyone help me..urgent...

saras

ursonlyriyaz
Starting Member

2 Posts

Posted - 2004-07-08 : 05:20:08

Hi Saras,

As the sp_executesql has return values of only 0 and 1

for success and failure respectively...u cannot get the output..

instead u can try the following if u need the output....

WHILE @n < @len
BEGIN
print Substring(@hex, @n * 4 + 1, 4)

SET @str = @str + N'Char(0x' + Substring(@hex, @n * 4 + 1, 4) + ') + '
print @str
SET @n = @n + 1
END

set @str = left(@str, len(@str)-2)

select @str

hope this helps....

sara_baboo
Starting Member

8 Posts

Posted - 2004-07-08 : 22:16:55

quote:
Originally posted by ursonlyriyaz

Hi Saras,

As the sp_executesql has return values of only 0 and 1

for success and failure respectively...u cannot get the output..

instead u can try the following if u need the output....

WHILE @n < @len
BEGIN
print Substring(@hex, @n * 4 + 1, 4)

SET @str = @str + N'Char(0x' + Substring(@hex, @n * 4 + 1, 4) + ') + '
print @str
SET @n = @n + 1
END

set @str = left(@str, len(@str)-2)

select @str

hope this helps....

thanks but it doesn't really help cos' select @str give the content of @str only..not executing it...

i am really loosing hope..
what to do?
do any body have any experience in sms application using sql

pls let me know.

sara

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-09 : 00:43:15

1) You can get output variables from sp_executesql. Here is Timmy's verison doing that (but the version I posted using a numbers table would be faster


declare @hex varchar(40), 
@n int,
@len int,
@str nvarchar(100),
@output varchar(20)

set @hex = '005400420045'
set @n = 0
set @len = len(@hex)/4

WHILE @n < @len
  SELECT @str = coalesce(@str,N'SELECT @output =') + N'Char(0x' + Substring(@hex, @n * 4 + 1, 4) + ') + ', @n = @n + 1

set @str = left(@str, len(@str)-2)

exec sp_executesql @str,N'@output varchar(20) output',@output output
select @output output

output               
-------------------- 
TBE

2) See Ehorns post here showing how to read http:// from T-SQL
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35900&whichpage=2[/url]

sara_baboo
Starting Member

8 Posts

Posted - 2004-07-12 : 00:23:42

thank to all..thank you very much....

saras

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources