Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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
SELECT @string
/*
Output:
no, it is bad. I disagree. I shan't do it
*/

Comments

Posted by jhonnydcano on 24 January 2009

Sorry for asking, but I don't see the wickedness of the query, can you please explain me further? thanks

Posted by charles.flock on 24 January 2009

There is nothing inherently 'wicked' about this. However, it does not appear that you have considered collation in this query. For example, you replace 'yes' with 'no', What about YES, Yes, yEs. . .. If you are case-sensitive you may want to add UPPER or LOWER to your SQL. And, what about yesterday? Isn't that going to become noterday? These are the kind of little things that you need to think about.

Posted by Phil Factor on 25 January 2009

I was told by an MVP that it only worked 'by chance' and was not 'supported' by Microsoft. Like you, I don't see anything wrong with it at all. I've used it for years.

I should have made plain that this is just an example to show the SQL. I'm not suggesting that you use it to turn positive statements into negative ones. Oh no.  I use it mostly for doing macro substitutions where the 'what' is something like '<%ErrorText%>'

Posted by ALZDBA on 28 January 2009

if it is straight forward, with no ambiguous combinations, it is surly a good and fast way of doing it.

[code]

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 'goodies', 'no goodies' 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 are goodies. I agree. I shall do it'

SELECT @String=REPLACE(@string,[what],[with]) FROM @Substitutions ORDER BY TheOrder

SELECT @string

[/code]

results into:

no, it are badies. I disagree. I shan't do it

            *****

Posted by vicky on 28 January 2009

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(lower(@string),lower([what]),[with]) FROM @Substitutions ORDER BY TheOrder

SELECT @string

Posted by karthikeyan on 4 July 2009

Phil,

Can you give me a real world example for mass replace()?

Suppose if there is a situation like this, I think either the DB was disgned wrongly or users are not aware of what they have to do.

Posted by Phil Factor on 4 July 2009

The real-world use I mentioned was in doing multi-lingual applications where a number of strings need to be easily rendered in several languages. I also use it in translating HTML entities in strings into Unicode, and in some tricky data feeds. It is quite possible that I've designed my DB badly, but I'm reluctant to agree that all use of this sort of approach shows bad db design.

Leave a Comment

Please register or log in to leave a comment.