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