November 30, 2017 at 9:22 am
Can you please help with converting Hex Value to ASCII Value using SQL Server ? below is the value
Hex Value : - 刀椀瘀攀爀猀椀搀攀
ASCII Value : - Riverside
November 30, 2017 at 9:32 am
Search this site for a splitter function to split the string into individual elements. Then convert each element to an integer - here's a page with a few ideas on how to do that. Use the CHAR function to convert each integer to a character. Finally, use a FOR XML query to concatenate it back into a single string.
John
November 30, 2017 at 10:12 am
Here's how:DECLARE @HEX_VALUE AS varchar(100) = '刀椀瘀攀爀猀椀搀攀'
SELECT CONVERT(varchar(20),
(
SELECT CHAR(TRY_CONVERT(int, TRY_CONVERT(varbinary, S.Item, 1)))
FROM dbo.DelimitedSplit8K(REPLACE(REPLACE(@HEX_VALUE, '00', ''), '&#', '0'), ';') AS S
WHERE S.Item <> ''
ORDER BY S.ItemNumber
FOR XML PATH('')
)) AS THE_STRING;
The string splitter function is Jeff Moden's from here:
http://www.sqlservercentral.com/articles/72993/
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 1, 2017 at 2:52 am
John Mitchell-245523 - Thursday, November 30, 2017 9:32 AMSearch this site for a splitter function to split the string into individual elements. Then convert each element to an integer - here's a page with a few ideas on how to do that. Use the CHAR function to convert each integer to a character. Finally, use a FOR XML query to concatenate it back into a single string.John
Thank you so much John for the right direction
December 1, 2017 at 2:52 am
sgmunson - Thursday, November 30, 2017 10:12 AMHere's how:DECLARE @HEX_VALUE AS varchar(100) = '刀椀瘀攀爀猀椀搀攀'SELECT CONVERT(varchar(20),
(
SELECT CHAR(TRY_CONVERT(int, TRY_CONVERT(varbinary, S.Item, 1)))
FROM dbo.DelimitedSplit8K(REPLACE(REPLACE(@HEX_VALUE, '00', ''), '&#', '0'), ';') AS S
WHERE S.Item <> ''
ORDER BY S.ItemNumber
FOR XML PATH('')
)) AS THE_STRING;The string splitter function is Jeff Moden's from here:
http://www.sqlservercentral.com/articles/72993/
thank you so much steve. it works great !!
December 6, 2017 at 12:21 pm
arunkk 80659 - Friday, December 1, 2017 2:52 AMsgmunson - Thursday, November 30, 2017 10:12 AMHere's how:DECLARE @HEX_VALUE AS varchar(100) = '刀椀瘀攀爀猀椀搀攀'SELECT CONVERT(varchar(20),
(
SELECT CHAR(TRY_CONVERT(int, TRY_CONVERT(varbinary, S.Item, 1)))
FROM dbo.DelimitedSplit8K(REPLACE(REPLACE(@HEX_VALUE, '00', ''), '&#', '0'), ';') AS S
WHERE S.Item <> ''
ORDER BY S.ItemNumber
FOR XML PATH('')
)) AS THE_STRING;The string splitter function is Jeff Moden's from here:
http://www.sqlservercentral.com/articles/72993/thank you so much steve. it works great !!
Glad I could help. Hope you enjoyed reading about the string splitter.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply