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
no, it is bad. I disagree. I shan't do it