Multiple Replace Statments for a String

Posted: October 1, 2010 in SQL Server 2005

If you need to replace multiple statements in a string, you can do this in one select statement as follows:

-- Declare variables
DECLARE @lv_replace_string VARCHAR(2000);

-- Initialise variables
SET @lv_replace_string = 'Test of good food';

-- Create table temp to hold XML nodes for merging
CREATE TABLE #tmp_replace(replace_string VARCHAR(200),
                          replace_with   VARCHAR(200));

-- Insert replace strings
INSERT INTO #tmp_replace (replace_string, replace_with) VALUES ('Test', 'Testing');
INSERT INTO #tmp_replace (replace_string, replace_with) VALUES ('Good', 'Bad');

-- Carry out multiple replaces in one select statement
SELECT @lv_replace_string = REPLACE(@lv_replace_string, r.replace_string, r.replace_with)
FROM   #tmp_replace r;

-- Show the result
SELECT @lv_replace_string;

-- Drop temp table
DROP TABLE #tmp_replace;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s