select cast(cast(0x416E6E61737461736961 as varbinary) as varchar)

?

/R

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.

-- 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

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