Ok... I was right about the cross-join... and it took more than 3 minutes to exec on 100,000 rows.

And when I change that snippet in the function to have a limit (see below), it still takes about 46 seconds (Richard's weigh's in at only 13).

select @output = @output + char(b.dec)

from dec2hex a, dec2hex b

where b.hex = substring(@input,a.dec*2+1,2)

AND a.dec*2+1 < LEN(@input)

order by a.dec

I think you're on the right track using a helper table to do this without a loop or dynamic SQL...

In case anyone else want's to do some testing on the problem, here's the code I used to gen the 100,000 row test table...

--===== Create and populate a 100,000 row test table.

 SELECT TOP 100000

        RowNum     = IDENTITY(INT,1,1),

        HexValue   = '416E6E61737461736961'

   INTO dbo.HexTest

   FROM Master.dbo.SysColumns t1,

        Master.dbo.SysColumns t2

--===== Add primary key

  ALTER TABLE dbo.HexTest

        ADD PRIMARY KEY CLUSTERED (RowNum)