Printed 2015/10/07 03:40PM

The Wickedness of the mass 'REPLACE()'

By Phil Factor, 2009/01/23

  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 @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
'shall', 'shan''t' UNION
'will', 'won''t' UNION
'can', 'can''t' UNION
'good', 'bad' UNION
'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
no, it is bad. I disagree. I shan't do it

Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.