I have been admonished in the past for the use of this code which makes as many

substitutions as you like in a string. However, It has worked on every version

of SQL Server I've ever tried it on. I've been using it for years. It

has an extraordinary number of uses.  Imagine you have a long string

and you need to change every occurence of  '(' into "\28", ')' to 

"\29",  '*' to  "\2a. You'd probably want to use this technique. I use

it where a website needs to be multilingual and I need to feed error

messages, in the users preferred language, to the front-end. It has all

sorts of uses in string manipulation.

DECLARE @String VARCHAR(MAX)
DECLARE @Substitutions TABLE (TheOrder INT IDENTITY(1,1) PRIMARY KEY, what VARCHAR(2000), [with] VARCHAR(2000))
--pop in some substitutions
INSERT INTO @Substitutions (what,[with])
   SELECT 'yes', 'no' UNION
   SELECT 'shall', 'shan''t' UNION
   SELECT 'will', 'won''t' UNION
   SELECT 'can', 'can''t' UNION
   SELECT 'good', 'bad' UNION
   SELECT 'agree', 'disagree'
SELECT @string='Yes, it is good. I agree. I shall do it'

SELECT @String=REPLACE(@string,[what],[with]) FROM @Substitutions ORDER BY TheOrder
SELECT @string
/*
Output:
no, it is bad. I disagree. I shan't do it
*/