http://www.sqlservercentral.com/blogs/philfactor/2009/01/23/the-wickedness-of-the-mass-replace/

Printed 2014/10/21 08:07AM

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 @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
*/

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