Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Converting Hexadecimal String Values to Alpha (ASCII) Strings

By Stephen Lasham,

Overview

On investigating some data, I came across a text column containing hexadecimal string information.A hexadecimal string consists of consecutive pairs of data written in base-16, with the symbols 09 and AF, where A equals 10, B equals 11, etc, up to F equals 15, i.e. "416E6E656C6F75697361".

It is possible to take each pair, convert it to decimal, and then to its ASCII character equivalent.Simple mathematics allows the conversion to decimal.As it is base-16, multiply the second column in from the right (sixteenths column) by 16, remembering A to F equals 10 to 15, and add on the value of the column on the right (unit column).

6E = (6 * 16) + 14 = 110
6F = (6 * 16) + 15 = 111

Next convert the decimal value to its ASCII equivalent using a conversion table, or in SQL the operation CHAR.

Select CHAR(110) = n
Select CHAR(111) = o

My string data consisted of ten pairs of hex data per row, which I required to convert to ASCII to make it readable in English.Investigating the Internet showed ways to convert character data to hexadecimal but not a lot the other way.I considered going through the string character pair by character pair and execute the above maths, but thought perhaps there may be a better way.

The online examples happily showed me information for converting a single hex character, where the hexadecimal two-character string is preceded by a 0x and is defined with a type of varbinary.

select char(cast(0x57 as varbinary)) -- = "W"

Unfortunately, the above example shows the hex pair as a constant not a variable, and on attempting to set a variable into the select statement, it failed as shown below.

Declare @pair as char(2)

Set @pair = '57'
select char(cast('0x' + @pair as varbinary)) -- = null

The only way I could see round this was to build the select into a statement string and execute it.

Declare @stmt nvarchar(500)
Declare @pair as char(2)

Set @pair = '57'
Set @stmt = 'select char(cast(0x' + @pair + ' as varbinary))'
execute sp_executesql @stmt -- = "W"

This actually works, but handling only one character (hex pair) at a time, thus I needed to do a pass of my sample table, and then do a character-by-character conversion of each string.The resulting code follows.

My Solution

As this is an example, it uses a sample table created below.This table contains three columns, a record id to sequence the row, the hex string to be converted, and an alpha string to hold the converted results.This is loaded with some sample data for conversion, and then follows the code to convert it.

/*
=================================
Create and load sample data table
=================================
*/
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)

/*
 =================
 Declare variables
 =================
*/

Declare @recordcount  as int
 , @maxrecords  as int
 , @stmt        as nvarchar(500)
 , @String        as varchar(20)
 , @StringLen        as int
 , @start        as int
 , @pair        as char(2)
 , @len  as int

Set @StringLen = 20 -- length of @string variable
/*
 ===============================================================
 Process the table converting Hex extra data table name to Alpha
 ===============================================================
*/
Set @maxrecords = (select count(*) from #HexToAlpha)
Set @recordcount = 1

While @recordcount <= @maxrecords
 Begin 
  Create table #result
 (
  recordid dec(5,0) identity(1,1)
 , result char(1)
 )
 Set @start = 1
 Set @String = (select hexstring from #HexToAlpha where recordID = @recordcount)
 Set @pair = (select substring(@String, @Start, 2))
 
 While @start < @StringLen
 Begin
 Set @stmt = 'insert into #result values(char(cast(0x' + @pair + ' as varbinary)))'
 execute sp_executesql @stmt
 Set @start = @start + 2
 Set @pair = substring(@String, @Start, 2)
  End

 Update #HexToAlpha 
        set alphastring = (Select max(case when recordid = 1 then result else '' end)
 + max(case when recordid = 2 then result else '' end)
 + max(case when recordid = 3 then result else '' end)
 + max(case when recordid = 4 then result else '' end)
 + max(case when recordid = 5 then result else '' end)
 + max(case when recordid = 6 then result else '' end)
 + max(case when recordid = 7 then result else '' end)
 + max(case when recordid = 8 then result else '' end)
 + max(case when recordid = 9 then result else '' end)
 + max(case when recordid = 10 then result else '' end)
 from #result) where recordID = @recordcount

 drop table #result

 Set @recordcount = @recordcount + 1
  end
/*
 ===================
 Present the results
 ===================
*/
Select * from #HexToAlpha

Drop table #HexToAlpha

If all is well, you will have a list of names following execution of this script.

Some Analysis

The biggest pain of this is to extract the characters one at a time. This required the need to create a temporary table (#result) to hold a record for each character in the string. Now my knowledge of SQL is sadly lacking, as I would have preferred to have only a single record or even a variable to hold the resulting string to which I could just concatenate each new successive character.

i.e. instead of

Set @stmt = 'insert into #result values(char(cast(0x' + @pair + ' as varbinary)))'

I would have preferred the result column to be 10 characters long and apply a statement as follows

Set @stmt = 'update #result set result = result + '
          + 'values(char(cast(0x' + @pair + ' as varbinary)))'

This however did not work, and if anyone can enlighten me as to why, I would love to know.

Instead, my code generates a #result table for each hex string, which looks like this.

For hex string 416E6E61737461736961

1, A
2, n
3, n
4, a
5, s
6, t
7, a
8, s
9, i
10, a

In this form, it is of little use and needs joining back into a single string. I used the unique identifier along with the max and case operations to concatenate the row values into a single string.

Update #HexToAlpha 
  set alphastring = (Select max(case when recordid = 1 then result else '' end)
 + max(case when recordid = 2 then result else '' end)
  + max(case when recordid = 3 then result else '' end)
 + max(case when recordid = 4 then result else '' end)
 + max(case when recordid = 5 then result else '' end)
 + max(case when recordid = 6 then result else '' end)
 + max(case when recordid = 7 then result else '' end)
 + max(case when recordid = 8 then result else '' end)
 + max(case when recordid = 9 then result else '' end)
 + max(case when recordid = 10 then result else '' end)
 from #result) where recordID = @recordcount

This provided me with the desired result, in this case "Annastasia".

An obvious problem presents itself with this solution, in that it is limited to fixed length hexadecimal strings, in this case 20 characters of hex into 10 characters of text. Increasing this means adding lines to the max/case statements above. My logic only needed to cater for the above lengths so this suited me fine.

Conclusion

I enjoyed the excursion away from the daily coding routines I normally work with and hope this code proves useful to others. I look forward to seeing alternative methods, including ones that can handle variable length strings.

If you only have one string to convert, a good online translator is available at http://www.defproc.co.uk/toys/hex.php

Total article views: 10889 | Views in the last 30 days: 9
 
Related Articles
FORUM

how to find last recordid

how to find last recordid

FORUM

Update Datetime Column to Empty String - No Error - Strange Results

Update Datetime Column to Empty String - No Error - Strange Results

FORUM

String splitter to table weird result

String Splitter

FORUM

string Comparison.

'string' = 'string___' ???

BLOG

Inserting Markup into a String with SQL

 There are a number of occasions when one might want to insert information at various places in a st...

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones