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
-Using this 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.