SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Turn hex into char string and concatenate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tonyb
Starting Member

2 Posts

Posted - 02/16/2006 :  17:06:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/16/2006 :  20:12:19  Show Profile  Reply with Quote
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 - 02/17/2006 :  09:30:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/17/2006 :  11:18:50  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000