Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Converting Hexadecimal String Values to Alpha (ASCII) Strings Expand / Collapse
Author
Message
Posted Friday, April 20, 2007 10:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 1, 2011 2:05 PM
Points: 32, Visits: 47
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/slasham/2969.asp


Post #360045
Posted Wednesday, May 16, 2007 11:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 6, 2008 2:00 PM
Points: 8, Visits: 12
did you try

select cast(cast(0x416E6E61737461736961 as varbinary) as varchar)

?

 

/R

Post #366639
Posted Thursday, May 17, 2007 12:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
He doesn't get it as a hex/binary number... he get's it as a string.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #366644
Posted Thursday, May 17, 2007 12:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655

Nicely done... If I may suggest, however, a method that avoids both loops and dynamic SQL...

--===== If the test table exists, drop it
     IF OBJECT_ID('TempDB..#HexToAlpha','U') IS NOT NULL
        DROP TABLE #HexToAlpha

--===== Supress the auto-display of rowcounts
    SET NOCOUNT ON

--===== This is your original table creation/population code
Create table #HexToAlpha
(
  recordid     dec(5,0) identity(1,1)
 , hexstring char(20)
 , alphastring char(10)
)
Insert into #HexToAlpha values('416E6E61737461736961', null)
Insert into #HexToAlpha values('416E6E656C6F75697361', null)
Insert into #HexToAlpha values('416E746F696E65747465', null)
Insert into #HexToAlpha values('4265726E616465747465', null)
Insert into #HexToAlpha values('4265726E617264696E65', null)
Insert into #HexToAlpha values('436872697374656C6C65', null)
Insert into #HexToAlpha values('4368726973746F706572', null)
Insert into #HexToAlpha values('43696E646572656C6C61', null)
Insert into #HexToAlpha values('436C656D656E74696E65', null)
Insert into #HexToAlpha values('4576616E67656C696E65', null)
Insert into #HexToAlpha values('4672616E636973637573', null)
Insert into #HexToAlpha values('467265646572696B7573', null)
Insert into #HexToAlpha values('4777656E646F6C696E65', null)
Insert into #HexToAlpha values('4A61637175656C696E65', null)
Insert into #HexToAlpha values('4B726973746F70686572', null)
Insert into #HexToAlpha values('4D617267756572697461', null)
Insert into #HexToAlpha values('4D617279636C61697265', null)
Insert into #HexToAlpha values('53656261737469616E6F', null)
Insert into #HexToAlpha values('536861756E74656C6C65', null)
Insert into #HexToAlpha values('5768696C68656D696E61', null)

--===== Ready to rock... setup a couple of variables
     -- Timer to measure duration
DECLARE @Start DATETIME
    SET @Start = GETDATE()

     -- The "key" to this demo and the speed of execution
DECLARE @Numbers VARCHAR(16)
    SET @Numbers = '0123456789ABCDEF'

--===== Demo the solution for 10 characters
 UPDATE #HexToAlpha
    SET alphastring =
      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 1,1),@Numbers,1) - 1)
                            + (CHARINDEX(SUBSTRING(HexString, 2,1),@Numbers,1) - 1)))
      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 3,1),@Numbers,1) - 1)
                            + (CHARINDEX(SUBSTRING(HexString, 4,1),@Numbers,1) - 1)))
      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 5,1),@Numbers,1) - 1)
                            + (CHARINDEX(SUBSTRING(HexString, 6,1),@Numbers,1) - 1)))
      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 7,1),@Numbers,1) - 1)
                            + (CHARINDEX(SUBSTRING(HexString, 8,1),@Numbers,1) - 1)))
      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString, 9,1),@Numbers,1) - 1)
                            + (CHARINDEX(SUBSTRING(HexString,10,1),@Numbers,1) - 1)))
      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,11,1),@Numbers,1) - 1)
                            + (CHARINDEX(SUBSTRING(HexString,12,1),@Numbers,1) - 1)))
      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,13,1),@Numbers,1) - 1)
                            + (CHARINDEX(SUBSTRING(HexString,14,1),@Numbers,1) - 1)))
      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,15,1),@Numbers,1) - 1)
                            + (CHARINDEX(SUBSTRING(HexString,16,1),@Numbers,1) - 1)))
      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,17,1),@Numbers,1) - 1)
                            + (CHARINDEX(SUBSTRING(HexString,18,1),@Numbers,1) - 1)))
      + CHAR(CONVERT(INT,16 * (CHARINDEX(SUBSTRING(HexString,19,1),@Numbers,1) - 1)
                            + (CHARINDEX(SUBSTRING(HexString,20,1),@Numbers,1) - 1)))
   FROM #HexToAlpha

--===== Display the duration
  PRINT STR(DATEDIFF(ms,@Start,GETDATE())) + ' Milliseconds duration'

--===== Display the final contents of the test table
 SELECT * FROM #HexToAlpha

If the number of characters becomes unpredictable, the use of a Tally table would make the solution almost as easy with only a minor sacrifice in speed.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #366648
Posted Thursday, May 17, 2007 4:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:04 AM
Points: 5, Visits: 27

-- Try to use this FUNCTION instead of any temptables, looping, dinamyc SQL and so on.

if exists (select * from dbo.sysobjects where name = 'f_hextostr' and xtype = 'FN')
drop function [dbo].[f_hextostr]
GO

CREATE FUNCTION [dbo].[f_hextostr] (@hexstring VARCHAR(512))
RETURNS VARCHAR(256)

AS

begin
 declare @char1 char(1), @char2 char(1), @strlen int, @currpos int, @result varchar(256)
 set @strlen=len(@hexstring)
 set @currpos=1
 set @result=''
 while @currpos<@strlen
  begin
   set @char1=substring(@hexstring,@currpos,1)
   set @char2=substring(@hexstring,@currpos+1,1)
   if (@char1 between '0' and '9' or @char1 between 'A' and 'F')
    and (@char2 between '0' and '9' or @char2 between 'A' and 'F')
    set @result=@result+
     char((ascii(@char1)-case when @char1 between '0' and '9' then 48 else 55 end)*16+
     ascii(@char2)-case when @char2 between '0' and '9' then 48 else 55 end)
   set @currpos = @currpos+2
  end
 return @result
end
GO

-- After filling rows of Your example, do simple this:

update #HexToAlpha set alphastring=dbo.f_hextostr(hexstring)

Best Regards: Richard

Post #366692
Posted Thursday, May 17, 2007 4:56 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 6, 2013 2:16 AM
Points: 693, Visits: 124
If you use dynamic SQL, you can use something like this:

DECLARE @String char(20), @stmt nvarchar(500)
SET @String='416E6E61737461736961'
IF @String LIKE '%[^0-9A-F]%' RETURN
SET @stmt='SELECT CONVERT(char(10),0x'+@String+')'
EXEC (@stmt)

And if you want to convert the whole table, use something like this:

Create table #HexToAlpha
(
recordid int identity(1,1)
, hexstring char(20)
, alphastring char(10)
)
Insert into #HexToAlpha values('416E6E61737461736961', null)
Insert into #HexToAlpha values('416E6E656C6F75697361', null)
Insert into #HexToAlpha values('416E746F696E65747465', null)
Insert into #HexToAlpha values('4265726E616465747465', null)
Insert into #HexToAlpha values('4265726E617264696E65', null)
Insert into #HexToAlpha values('436872697374656C6C65', null)
Insert into #HexToAlpha values('4368726973746F706572', null)
Insert into #HexToAlpha values('43696E646572656C6C61', null)
Insert into #HexToAlpha values('436C656D656E74696E65', null)
Insert into #HexToAlpha values('4576616E67656C696E65', null)
Insert into #HexToAlpha values('4672616E636973637573', null)
Insert into #HexToAlpha values('467265646572696B7573', null)
Insert into #HexToAlpha values('4777656E646F6C696E65', null)
Insert into #HexToAlpha values('4A61637175656C696E65', null)
Insert into #HexToAlpha values('4B726973746F70686572', null)
Insert into #HexToAlpha values('4D617267756572697461', null)
Insert into #HexToAlpha values('4D617279636C61697265', null)
Insert into #HexToAlpha values('53656261737469616E6F', null)
Insert into #HexToAlpha values('536861756E74656C6C65', null)
Insert into #HexToAlpha values('5768696C68656D696E61', null)

WHILE 1=1 BEGIN
DECLARE @String char(20), @stmt nvarchar(500), @recordid int

SET @recordid=NULL
SELECT TOP 1 @String=hexstring, @recordid=recordid
FROM #HexToAlpha WHERE alphastring IS NULL
IF @recordid IS NULL BREAK

IF @String LIKE '%[^0-9A-F]%' RETURN

SET @stmt='UPDATE #HexToAlpha SET alphastring=CONVERT(char(10),0x'
+@String+') WHERE recordid='+CONVERT(varchar(10),@recordid)
EXEC (@stmt)
END

SELECT * FROM #HexToAlpha

By checking the string against the '%[^0-9A-F]%' pattern, I've tried to avoid any possibility of SQL Injection due to the usage of Dynamic SQL.

Razvan



Post #366706
Posted Thursday, May 17, 2007 5:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 16, 2014 3:57 AM
Points: 379, Visits: 55
or you can build the entire update string at once:

declare @sql nvarchar(4000)
set @sql = ''
select @sql = @sql + ' update #HexToAlpha set alphastring = convert(varchar, 0x' + hexstring + ') where recordid = ' + convert(varchar, recordid)
from #HexToAlpha

exec sp_executesql @sql
Post #366708
Posted Thursday, May 17, 2007 5:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
Heh... I thought you said "without looping".

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #366711
Posted Thursday, May 17, 2007 5:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
quoteBy checking the string against the '%[^0-9A-F]%' pattern, I've tried to avoid any possibility of SQL Injection due to the usage of Dynamic SQL.
Nice!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #366714
Posted Thursday, May 17, 2007 5:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:04 AM
Points: 5, Visits: 27

Excuse Me!

Maybe my english is poor (or too short), I'm from Hungary.

I mean "looping" --> looping on records. The "function" solution loops on simple variables only (in memory), and according to my experiences, its the fastest way.

Thanks: Richard

Post #366715
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse