April 20, 2007 at 10:41 am
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/slasham/2969.asp
May 16, 2007 at 11:14 pm
did you try
select
cast(cast(0x416E6E61737461736961 as varbinary) as varchar)
?
/R
May 17, 2007 at 12:17 am
He doesn't get it as a hex/binary number... he get's it as a string.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2007 at 12:49 am
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
Change is inevitable... Change for the better is not.
May 17, 2007 at 4:26 am
-- 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
May 17, 2007 at 4:56 am
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
May 17, 2007 at 5:00 am
May 17, 2007 at 5:17 am
Heh... I thought you said "without looping".
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2007 at 5:21 am
![]() | 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
Change is inevitable... Change for the better is not.
May 17, 2007 at 5:22 am
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
May 17, 2007 at 5:25 am
That also takes care of the problem when unpredictable lengths occur without a hint of a loop. Nicely done!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2007 at 8:16 am
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
May 17, 2007 at 8:28 am
why not use Ryan Price's solution?
May 17, 2007 at 10:43 am
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'
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
May 17, 2007 at 2:07 pm
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 32 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy