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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

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 
 DECLARE @vb varbinary(8) 
 SET @vb = CONVERT(varbinary(8),@i) 
 DECLARE @hx varchar(30) 
 EXEC master..xp_varbintohexstr @vb, @hx OUT 
 RETURN @hx 

-Using this table-

create table 
(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) 


Flowing Fount of Yak Knowledge

6065 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
Go to Top of Page

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

Flowing Fount of Yak Knowledge

6065 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000