Here you go. No loops, cursors or even (incredibly fast) nested REPLACE:

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

IF OBJECT_ID('TempDB..#replacements','U') IS NOT NULL

DROP TABLE #replacements

--===== Create the test table with

CREATE TABLE #mytable

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

LongString nvarchar(max)

)

--===== Create table with replacements

CREATE TABLE #replacements

(

code nvarchar(55)

,replacement nvarchar(255)

)

--===== Add sample data

INSERT INTO #mytable (LongString)

select 'code01 something else code02' union all

select 'code02 code02 aacode01'

INSERT INTO #replacements

select 'code01', 'XXXX' union all

select 'code02', 'YYYY'

-- solution

;WITH Tally AS (SELECT top 100 n = ROW_NUMBER() OVER(ORDER BY [name]) from master.dbo.syscolumns),

NormalisedData AS (

SELECT m.ID,

WordID = ROW_NUMBER() OVER(PARTITION BY m.ID ORDER BY t.n),

m.longstring,

word = SUBSTRING(m.longstring, t.n,

(ISNULL(NULLIF(CHARINDEX(' ', SUBSTRING(m.longstring, t.n, LEN(m.LongString)-t.n)), 0),

LEN(SUBSTRING(m.longstring, t.n, LEN(m.LongString)-t.n))+2)) - 1)

FROM #mytable m

INNER JOIN Tally t ON SUBSTRING(' ' + m.LongString, t.n, 1) = ' '

WHERE t.n <= LEN(m.LongString)

),

ReplacedData AS (

SELECT d.ID, d.WordID, d.LongString, NewWord = ISNULL(r.replacement, d.word)

FROM NormalisedData d

LEFT JOIN #replacements r ON r.code = d.word

)

SELECT d.ID, OldString = d.LongString,

NewString = (

SELECT ' ' + NewWord

FROM ReplacedData r

WHERE r.ID = d.ID

ORDER BY ID, WordID

FOR XML PATH(''))

FROM ReplacedData d

GROUP BY d.ID, d.LongString