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
 General SQL Server Forums
 New to SQL Server Programming
 Turn hex into char string and concatenate

Author  Topic 

tonyb
Starting Member

2 Posts

Posted - 2006-02-16 : 17:06:01
I've been trying to return hex data in a way that can be concatenated.
I need the actual hex info (e.g. 0x6E3C070) as displayed
since it contains info about the path to a file.
So I can turn it into D:\6E\3C\07 to get the path to the file.
In searching around I have come across a way to do this but can't figure out how to get it to run through a column and either display or insert into a table multiple results.

-Here's the user function that converts an integer into a hex string-

CREATE FUNCTION udf_hex_string (@i int)
RETURNS varchar(30) AS
BEGIN
DECLARE @vb varbinary(8)
SET @vb = CONVERT(varbinary(8),@i)
DECLARE @hx varchar(30)
EXEC master..xp_varbintohexstr @vb, @hx OUT
RETURN @hx
END
GO


-Using this table-

create table
XPages
(PageStoreId int null,
HexString varchar (30) null,
VolumePath varchar (60) null, )


--'PageStoreId' contains the data that needs to be converted into the editable hex string
--'HexString' is where I'd like it to go so I can parse it later.

--I can run the below select and get the hex string. But am stuck on how to run a select or update that would run through the 'XPages.PagestoreId'
column and insert the hex string into the 'XPages.Hexstring' column. 'XPages.PagestoreId' could have 100's of entries that need to be converted and placed in the relevant the 'XPages.Hexstring' column.


SELECT dbo.udf_hex_string(1234)



Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-16 : 20:12:19
Looks like you've got all the hard stuff figured out already. Are you saying that all you need is this?

update xpages set
HexString = dbo.udf_hex_string(PageStoreID)
from xpages
where HexString is null
and PageStoreID > 0


Be One with the Optimizer
TG
Go to Top of Page

tonyb
Starting Member

2 Posts

Posted - 2006-02-17 : 09:30:56
Yeah, that was all I was needing.
I'm learning as I'm going and for some reason I couldn't come up with the update.

Thanks TG.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-17 : 11:18:50
Actually, I should thank you. I didn't know about the extended stored procedure 'xp_varbintohexstr'.
I wrote my own some time ago when I needed that functionality...oh well.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -