SQL Server 2008 R2 – Replace Value Using Several Criteria in Another Table

sql-server-2008-r2

Brief illustration :
If I want to replace a single string using multiple criteria from another table, I can use this statement :

-- assume table_2 has column 'value' containing records such as 'd', 'f','h'.
declare @x varchar(10)
set @x = 'abcdefghij'

Select @x = replace(@x,value,'') from table_2
Select @x
-- the result will be : abcegij.

Question :
How do I implement this if I have many initial strings i.e the 'abcdefghij' one from above example, stored in a table (table_1) and would like to be replaced for any occurrence of strings contained in another table (Table_2)

Edit :
DBMS being used in this question is sql server 2008 r2.
My current solution is somewhat similar to what JoseTeixeira has proposed i.e using cursor, manually iterating each record in table_1, process it using above snippet and then store each result into a temporary table. In other words, this temporary table will hold all records from table_1 which have been cleansed (replaced for any string occurrence) using "dictionary" in table_2.

And perhaps this is the most important thing I have missed when asking this question.. I'm looking for a solution without using cursor iteration.

Best Answer

Creating the schema - Tables that will be used for the example:

DECLARE @table_1 TABLE (
    someText VARCHAR(10)
)
INSERT INTO @table_1 values ('abcdefghij')
INSERT INTO @table_1 values ('fajiefhda')
INSERT INTO @table_1 values ('hurineajsd')

DECLARE @table_2 TABLE (
    value VARCHAR(10)
)
INSERT INTO @table_2 values ('d')
INSERT INTO @table_2 values ('f')
INSERT INTO @table_2 values ('h');

Procedural code starts here. First a temporary table is used to get the replace strings numbered so we can iterate on them:

DECLARE @table_2_numbered TABLE (
    RowNum int,
    value VARCHAR(10)
);

INSERT INTO @table_2_numbered
SELECT ROW_NUMBER() OVER (ORDER BY value ASC) AS RowNum, value
FROM @table_2;

The following CTE will pick up the original strings that we want to update (@table_1) and will iterate through each row of the table with the replacing strings, which are now numbered (@table_2_numbered), and recursively apply them to replace every desired occurence. Notice we keep track of the original strings so we know which replaced strings correspond to which original strings:

WITH CTE (originalText, replaced, iteration)
AS
(
    SELECT someText AS originalText
        , someText AS replaced
        , 1 AS iteration
    FROM @table_1 t1
    UNION ALL
    SELECT CTE.originalText
        , CAST(REPLACE(CTE.replaced, value, '') AS VARCHAR(10)) AS replaced
        , iteration + 1 AS iteration
    FROM CTE, @table_2_numbered t2
    WHERE t2.RowNum = iteration
)
UPDATE t1 SET t1.someText = CTE.replaced
FROM @table_1 t1, CTE
WHERE CTE.iteration = (SELECT MAX(RowNum)+1 FROM @table_2_numbered)
    AND t1.someText = CTE.originalText

SELECT * FROM @table_1

And here are the results:

someText
--------
abcegij
ajiea
urineajs
Related Question