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