Converting Hexadecimal String Values to Alpha (ASCII) Strings
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/slasham/2969.asp
-
did you try
select
cast(cast(0x416E6E61737461736961 as varbinary) as varchar)
?
/R
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
-- 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
-
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
-
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
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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!
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
-
That also takes care of the problem when unpredictable lengths occur without a hint of a loop. Nicely done!
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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 sysobjects
declare @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)*2
drop table #NHC
select * from #hextoalpha
-
why not use Ryan Price's solution?
-
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
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
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)
as
begin
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_value
end -- hexstring_to_binary
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply