SQLServerCentral / Discuss Content Posted by Stephen Lasham / Article Discussions / Article Discussions by Author / Converting Hexadecimal String Values to Alpha (ASCII) Strings / Latest Posts

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Lashams — Sun, 27 Apr 2008 14:12:14 GMT

Agree that presentation software is great for presenting end results data, but in some instances you want to have pre-filtered the results from your database via the underlying SQL. This was why I needed the conversion in the SQL, as I had to filter my table to get just a few rows out of a much larger table, and just present these few rows by the presentation software. I believe doing the filtering in the SQL on the server to be a lot quicker than passing the whole table back to the presentation software to filter it there. Thanks for the feedback.

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Fri, 25 Apr 2008 06:20:13 GMT

I agree that there're a lot of things, like this conversion, that should be done in the GUI instead of SQL Server. Displaying commas in dollar amounts and formatting dates and the like qualify, as well. That does two very good things... it distributes the "extra" clock cycles to the GUI thereby taking some of the load off of the server and it allows the GUI to format according to regional settings...... if you have a GUI.If all you have is batch code, then finding a good high speed answer in T-SQL is worth it. And, no, CLR's aren't the Nirvana of code that everyone is looking for, either. Source code external to the Server must be maintained and they ususally can't beat a properly written solution in T-SQL. Matt Miller, me, and a couple of other folks did some pretty heavy testing against CLR's and the only one that came out better was a RegEx CLR that did replacements (I can't put my hands on the URLs... I'd have to do a search). Of course, just because someone says something like this doesn't mean you shouldn't also do some tests of your own for you own particular situation.

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

mike brockington — Fri, 25 Apr 2008 04:32:38 GMT

Surely the real lesson from this should be that if something is this hard in SQL you should be doing it in a different language? I rarely come across an application that is written purely in SQL, so there is usually a presentation layer that is more capable of handling what looks like a purely presentational issue.

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Thu, 28 Jun 2007 23:02:00 GMT

Thanks for coming back with that... lots of us end up wondering if we actually helped or confused. Really appreciate the feedback even if it is a bit later than most.

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Lashams — Thu, 28 Jun 2007 19:09:00 GMT

Firstly my apologies for not responding sooner

Secondly, thank you all for the marvellous responses and ensuing competition. Sadly I must now say my original SQL was a very poor contender over 1,000,000 records, and some of the alternatives, well let's just say WOW!

I have really learned some stuff from you people, so much appreciated.

Stephen

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Dennis D. Allen — Mon, 28 May 2007 16:15:00 GMT

I have found using STUFF to create the output to be a little bit faster. I am assuming that it is due to traditional string concantination issues...

ALTER FUNCTION hex2alpha( @input VARCHAR(256) ) RETURNS VARCHAR(256) ASBEGIN    DECLARE @output VARCHAR(256); SET @output = SPACE(LEN(@input)/2)    SELECT @output = STUFF(@output,a.dec+1,1,CHAR(b.dec))    FROM         dec2hex AS a        INNER JOIN dec2hex AS b ON b.hex = SUBSTRING(@input,a.dec*2+1,2)    WHERE        a.dec < LEN(@input)/2    ORDER BY a.dec    RETURN @outputEND

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Eddie Lee — Wed, 23 May 2007 07:31:00 GMT

Richard yes, your loop does prove your fn to be a bit quicker. Weird how the run time of mine fluctuates so?!

Ho hum.

Good chatting.

Regards, Eddie.

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Richard Dragossy-168541 — Tue, 22 May 2007 09:45:00 GMT

Hey Eddie!

I like this conversation very much!

Try this with only 100.000 recs!

if object_id ('tempdb.dbo.#TimeTrial') is not null drop table #TimeTrialcreate table #TimeTrial (id integer identity(1,1), Event varchar (20), datestart datetime default getdate (), dateend datetime null)

declare @counter integerset @counter=1while @counter<13 begin insert #TimeTrial (Event) values ('Richard') update HexTest set ASCIIValue = dbo.f_hextostr (HexValue) update #TimeTrial set dateend=getdate() where id=@counter set @counter=@counter+1

insert #TimeTrial (Event) values ('Eddie') update HexTest set ASCIIValue = dbo.fn_HexToAlpha (HexValue) update #TimeTrial set dateend=getdate() where id=@counter set @counter=@counter+1 end

select *, dateend-datestart as diff from #TimeTrial order by id

The result:

1 Richard 00:00:17.3332 Eddie 00:00:08.2833 Richard 00:00:06.6104 Eddie 00:00:08.0105 Richard 00:00:06.4206 Eddie 00:00:08.0007 Richard 00:00:06.5208 Eddie 00:01:02.3609 Richard 00:00:06.46010 Eddie 00:00:07.78011 Richard 00:00:06.51012 Eddie 00:00:07.803

Conclusions: The first runs (1-2) must be eliminated, our data not in cash yet. The runs 7-8 must be eliminated, Your result totally differs from others ( really sometimes I don't know, what the hell is running on background), the rest results are comparable.

By the way: our functions are able to run faster, if:

You eliminate the UPPER function (suppose the HEX string is correct);

I eliminate the IF part (suppose the HEX string is correct)

Best regards: Richard

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Eddie Lee — Tue, 22 May 2007 08:39:00 GMT

I ran mine and Richard's functions on Jeffs test data (pushed up to 1000000 rows) and my function above (at 00:01:25.603) just pipped Richards (at 00:01:57.740).

alter table #HexTest add ASCIIValue varchar (20)go

if object_id ('tempdb.dbo.#TimeTrial') is not null drop table #TimeTrialcreate table #TimeTrial (Event varchar (20), DateStamp datetime default getdate ())

update #HexTest set ASCIIValue = null -- resetinsert #TimeTrial (Event) values ('RichardStart')update #HexTest set ASCIIValue = dbo.f_hextostr (HexValue)insert #TimeTrial (Event) values ('RichardFinish')select * from #HexTest

update #HexTest set ASCIIValue = null -- resetinsert #TimeTrial (Event) values ('EddieStart')update #HexTest set ASCIIValue = dbo.fn_HexToAlpha (HexValue)insert #TimeTrial (Event) values ('EddieFinish')select * from #HexTest

select (select DateStamp from #TimeTrial where Event = 'RichardFinish') - (select DateStamp from #TimeTrial where Event = 'RichardStart') Richardselect (select DateStamp from #TimeTrial where Event = 'EddieFinish') - (select DateStamp from #TimeTrial where Event = 'EddieStart') Eddie

Eddie.

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Eddie Lee — Tue, 22 May 2007 06:17:00 GMT

Another function for the mix:

if objectproperty (object_id ('dbo.fn_HexToAlpha'), 'istablefunction') = 0 drop function dbo.fn_HexToAlphagocreate function dbo.fn_HexToAlpha (@HexString char (40))returns varchar (20) asbegindeclare @Loop tinyint, @AsciiString varchar (20), @Nibble1 char (1), @Nibble2 char (1)select @AsciiString = '', @Loop = 1while @Loop < len (@HexString) begin select @Nibble1 = upper (substring (@HexString, @Loop, 1)), @Nibble2 = upper (substring (@HexString, @Loop + 1, 1)) set @AsciiString = @AsciiString + char (16 * case @Nibble1 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else @Nibble1 end + case @Nibble2 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else @Nibble2 end) set @Loop = @Loop + 2 endreturn @AsciiStringendgoselect dbo.fn_HexToAlpha (HexString) from #HexToAlpha

Eddie Lee.

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Sat, 19 May 2007 18:06:00 GMT

Heh... and I was too sleepy this morning... I shouldn't post when I'm pooped...

It is still a bit of a half cross join (more specifically, a triangular join and does an index scan of 76 rows) but it certainly isn't 65,536. My mistake. That would also explain why Richard's is still faster...

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

JH Park — Sat, 19 May 2007 13:53:00 GMT

Thank you Jeff, I was too sleepy to run comparison tests myself last night.Your input length condition is a nice touch.But no Jeff, it not a cross-join nor generates 65,536 internal rows. It's a n inner join. You can see it if you check out its execution plan, and it can be replaced with:---------------------------------------- select @output = @output + char(b.dec) from dec2hex a inner join dec2hex b on b.hex = substring(@input,a.dec*2+1,2) where a.dec*2+1 < LEN(@input) order by a.dec----------------------------------------without affecting its semantics at all.And dec2hex table is looked-up just LEN(@output) times.(It was 256 times before you add the criteria, though.)I think it's weird that Richard's function is still much faster. I thought the join routine of the server should be most efficient. Moreover, my function calls substring() half the times of Richard's.Anyway, it was a very interesting quiz. Thank you all!

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Sat, 19 May 2007 10:54:00 GMT

Ok... I was right about the cross-join... and it took more than 3 minutes to exec on 100,000 rows.

And when I change that snippet in the function to have a limit (see below), it still takes about 46 seconds (Richard's weigh's in at only 13).

select @output = @output + char(b.dec)from dec2hex a, dec2hex bwhere b.hex = substring(@input,a.dec*2+1,2)AND a.dec*2+1 < LEN(@input)order by a.dec

I think you're on the right track using a helper table to do this without a loop or dynamic SQL...

In case anyone else want's to do some testing on the problem, here's the code I used to gen the 100,000 row test table...

--===== Create and populate a 100,000 row test table. SELECT TOP 100000 RowNum = IDENTITY(INT,1,1), HexValue = '416E6E61737461736961' INTO dbo.HexTest FROM Master.dbo.SysColumns t1, Master.dbo.SysColumns t2

--===== Add primary key ALTER TABLE dbo.HexTest ADD PRIMARY KEY CLUSTERED (RowNum)

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Sat, 19 May 2007 10:41:00 GMT

Sure thing, JH... I don't like the looks of that unconstrained cross-join in the function, though... I think it's going to gen 65,536 internal rows every time it executes...

I'll try without and with a limit in the criteria...

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

JH Park — Sat, 19 May 2007 03:53:00 GMT

How about this? No dynamic SQL, no (explicit) loop. Just a permanent lookup table. ====================================================================declare @dec2hex1 table ( id smallint identity(0,1), hex1 char(1))insert into @dec2hex1 (hex1)select top 16 null from sysobjectsupdate @dec2hex1 set hex1 = substring('0123456789abcdef',id+1,1)create table dec2hex ( dec smallint identity(0,1), hex char(2))insert into dec2hex (hex)select a.hex1+b.hex1 from @dec2hex1 a cross join @dec2hex1 b order by 1create unique index ix_dec2hex on dec2hex (hex, dec);create function hex2alpha ( @input varchar(256)) returns varchar(256)begin declare @output varchar(256) set @output = '' select @output = @output + char(b.dec) from dec2hex a, dec2hex b where b.hex = substring(@input,a.dec*2+1,2) order by a.dec return @outputendselect dbo.hex2alpha('416E6E61737461736961')============================================Jeff, can you run a performance test for this?

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Fri, 18 May 2007 21:20:00 GMT

Hey, Richard... just to let you know... I can normally beat most looping functions by using a Tally table... but not this one (least ways, not so far ). Your looping function is about 2 seconds faster that my Tally table solution on a 100,000 row test table. Nice job!

FYI... the direct fixed width solution I posted smokes both the Tally table and the looping inside the function by a full 10 out of 13 seconds.

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Pratap Prabhu — Fri, 18 May 2007 15:16:00 GMT

below a function I have been using in a App. Its quite similar to yours. Its fast as it only loops in memory and does not require temp tables. as usual usage examples are in the remarks on the top of the function. The input string size is 4000 characters, therefore it can only return an ascii string of 2000 characters ie 1/2 the size passed in. secondly, if the string passed in is of an odd length, it left pads a 0 before iterating so that it does not crash

create function Hex2String(@strHex as nvarchar(4000))returns nvarchar(2000)asbegin /* Function : Hex2String Author : Pratap J Prabhu Syntax : > select dbo.Hex2String('6566676869') > declare @strHexString nvarchar(4000) declare @strReturnedString nvarchar(4000) set @strHexString ='65666768' set @strReturnedString = dbo.Hex2String(@strHexString) print @strReturnedString Purpose : passed a hexadecimal encoded string this function returns a Ascii string Limitations : Max input Hex string size = 4000 characters Max string size returned = 2000 */ declare @cChar char(1) declare @nDeciVal int declare @cHexBit1 char(1) declare @cHexBit2 char(1) declare @strRetVal nvarchar(2000) declare @iLoop smallint set @strRetVal='' set @strHex = ltrim(rtrim(isnull(@strHex,''))) if @strHex<>'' begin ----- Hex numbers should always have a even length, so lets make it so if (round(len(@strHex)/2,0)*2)<>len(@strHex) set @strHex='0'+@strhex

----- Now loop down the length of the HexString handling 2 bits at a time set @iLoop=1 while @iLoop <len(@strHex) begin set @cHexBit1=substring(@strHex, @iLoop , 1) -- The First Bit set @cHexBit2=substring(@strHex, @iLoop+1, 1) -- the second bit

-- Convert the First Hex Bit value to its equivalent Decimal Value -- Multiplying by 16 as its in the 10s place if @cHexBit1>=0 and @cHexBit1<=9 set @nDeciVal=convert(int,@cHexBit1)*16 else set @nDeciVal=convert(int,ASCII(@cHexBit1)-ASCII('A')+1) * 16 -- Convert the second Hex Bit value to its equivalent Decimal Value -- do not Multiply by 16 as its in the units place if @cHexBit2>=0 and @cHexBit2<=9 set @nDeciVal=@nDeciVal+convert(int,@cHexBit2) else set @nDeciVal=@nDeciVal+(ASCII(@cHexBit2)-ASCII('A')+1) -- Store the Ascii Value set @strRetVal=@strRetVal+ char(@nDeciVal) set @iLoop=@iLoop+2 -- continue to the next character ie the next 2 Hex Bits end end return (@strRetVal)end

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Steven Hanley — Thu, 17 May 2007 14:07:00 GMT

How about:

select convert(varchar,dbo.hexstring_to_binary('0x' + '416E6E61737461736961'))

Here's the function:

ALTER function [dbo].[hexstring_to_binary] ( @hex_string varchar(max) )returns varbinary(max)asbegin declare @hex char(2) declare @position int declare @count int declare @binary_value varbinary(max) set @count = len(@hex_string) set @binary_value = cast('' as varbinary(1))

if substring(@hex_string, 1, 2) = '0x' set @position = 3 else set @position = 1 while (@position <= @count) begin set @hex = substring(@hex_string, @position, 2) set @binary_value = @binary_value + cast(case when substring(@hex, 1, 1) like '[0-9]' then cast(substring(@hex, 1, 1) as int) else cast(ascii(upper(substring(@hex, 1, 1)))-55 as int) end * 16 + case when substring(@hex, 2, 1) like '[0-9]' then cast(substring(@hex, 2, 1) as int) else cast(ascii(upper(substring(@hex, 2, 1)))-55 as int) end as binary(1)) set @position = @position + 2 end return @binary_valueend -- hexstring_to_binary

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Gordon Pollokoff — Thu, 17 May 2007 10:43:00 GMT

No loops, minimal dynamic sql

Create table #HexToAlpha

(

recordid dec(5,0) identity(1,1)

, hexstring char(20)

, alphastring varchar(20)

)

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)

DECLARE @SQL NVARCHAR(1000)

--SET @SQL = 'update #HexToAlpha set alphastring = cast(cast((''0x'' + hexstring) as varbinary) as varchar)'

create TABLE #tempsql

(

recordid INT,

sql NVARCHAR(500)

)

SET @SQL = 'insert into #tempsql select recordid,''update #HexToAlpha set alphastring = cast(cast((0x'' + hexstring +'') as varbinary) as varchar)'' from #HexToAlpha'

PRINT @SQL

EXEC sp_executesql @SQL

EXEC sp_execresultset 'select sql + '' where recordid = '' + cast(recordid as nvarchar) from #tempsql'

SELECT * FROM #HexToAlpha

DROP TABLE #HexToAlpha

DROP TABLE #tempsql

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Charles Murugu — Thu, 17 May 2007 08:28:00 GMT

why not use Ryan Price's solution?

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jim J — Thu, 17 May 2007 08:16:00 GMT

1 (small) loop (but not row-by-row), using temporary lookup table, can handle variable lengths-------------------------------------------------------------Create table #HexToAlpha( recordid dec(5,0) identity(1,1) , hexstring varchar(20) , alphastring varchar(10))Insert into #HexToAlpha values('416E6E61', '') Insert into #HexToAlpha values('416E6E656C6F75697361', '')Insert into #HexToAlpha values('416E746F696E65747465', '')Insert into #HexToAlpha values('4265726E616465747465', ''Insert into #HexToAlpha values('4265726E617264696E65', '') --etc...create table #NHC (numbr smallint identity(0,1), hex char(2), chr varchar(1))insert into #NHC (chr) select top 256 null from sysobjectsdeclare @hexes char(16)set @hexes = '0123456789ABCDEF'update #NHC set hex = substring(@hexes,numbr/16+1,1)+ substring(@hexes, numbr- (numbr/16 * 16)+1, 1), chr=char(numbr)while exists (select * from #hextoalpha where len(hexstring) <> len(alphastring)*2) update #hextoalpha set alphastring= alphastring + (select chr from #NHC where substring(hexstring, len(alphastring)*2 +1,2) = hex ) where len(hexstring) <> len(alphastring)*2drop table #NHCselect * from #hextoalpha

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Thu, 17 May 2007 05:25:00 GMT

That also takes care of the problem when unpredictable lengths occur without a hint of a loop. Nicely done!

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Richard Dragossy-168541 — Thu, 17 May 2007 05:22:00 GMT

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

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Thu, 17 May 2007 05:21:00 GMT

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.

Nice!

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Thu, 17 May 2007 05:17:00 GMT

Heh... I thought you said "without looping".

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Norman Rasmussen — Thu, 17 May 2007 05:00:00 GMT

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 #HexToAlphaexec sp_executesql @sql

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Razvan Socol — Thu, 17 May 2007 04:56:00 GMT

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]%' RETURNSET @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)ENDSELECT * FROM #HexToAlphaBy 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

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Richard Dragossy-168541 — Thu, 17 May 2007 04:26:00 GMT

-- 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)

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 @resultendGO

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

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

Best Regards: Richard

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Thu, 17 May 2007 00:49:00 GMT

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 codeCreate 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 durationDECLARE @Start DATETIME SET @Start = GETDATE()

-- The "key" to this demo and the speed of executionDECLARE @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.

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Jeff Moden — Thu, 17 May 2007 00:17:00 GMT

He doesn't get it as a hex/binary number... he get's it as a string.

RE: Converting Hexadecimal String Values to Alpha (ASCII) Strings

Ryan Price — Wed, 16 May 2007 23:14:00 GMT

did you try

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

Converting Hexadecimal String Values to Alpha (ASCII) Strings

Lashams — Fri, 20 Apr 2007 10:41:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/slasham/2969.asp