http://www.sqlservercentral.com/blogs/philfactor/2009/01/23/the-wickedness-of-the-mass-replace/ Printed 2015/04/27 11:22AM
The Wickedness of the mass 'REPLACE()'
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