SQL Server multiple REPLACE with #temp table

Here is one way using CROSS APPLY

SELECT result FROM   #temp2 t2        CROSS apply (SELECT Replace(string_name, t1.string_to_replace, '') AS                            result                     FROM   #temp t1) cs WHERE  result <> string_name 

Result :

result-----ABCDEFGHI

Note : This will work only if the each string_name has only one string_to_replace

Update : To handle more than one string_to_replace in a single string_name here is one way using Dynamic sql

I have made one small change to the #temp table by adding a identity property to loop

IF Object_id('tempdb..#temp') IS NOT NULL   DROP TABLE #temp IF Object_id('tempdb..#temp2') IS NOT NULL   DROP TABLE #temp2 CREATE TABLE #temp   (      id                INT IDENTITY(1, 1),      string_to_replace NVARCHAR(5)   ) INSERT INTO #temp             (string_to_replace) VALUES      (' '),             ('/'),             ('_') CREATE TABLE #temp2   (      string_name NVARCHAR(5)   ) INSERT INTO #temp2             (string_name) VALUES      ('A BC'),             ('D/EF'),             ('G_HI'),             ('A BD_') DECLARE @col_list          VARCHAR(8000)= '',         @sql               VARCHAR(max),         @cntr              INT,         @inr               INT =1,         @STRING_TO_REPLACE NVARCHAR(5) SELECT @cntr = Max(id) FROM   #temp SET @sql = 'select ' WHILE @inr < = @cntr   BEGIN       SELECT @STRING_TO_REPLACE = string_to_replace       FROM   #temp       WHERE  id = @inr       IF @inr = 1         SET @col_list = 'replace (STRING_NAME,'''                         + @STRING_TO_REPLACE + ''','''')'       ELSE         SET @col_list = 'replace (' + @col_list + ','''                         + @STRING_TO_REPLACE + ''','''')'       SET @inr+=1   END SET @sql += ' from #temp2' --print @col_list SET @sql = 'select ' + @col_list + ' as Result from #temp2' --print @sql EXEC (@sql) 

Result :

Result------ABCDEFGHIABD

The multiple replace can be achieved via a recursive CTE as per following example:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #tempIF OBJECT_ID('tempdb..#temp2') IS NOT NULL DROP TABLE #temp2CREATE TABLE #temp(    STRING_TO_REPLACE NVARCHAR(10)    ,Pattern NVARCHAR(10))INSERT INTO #temp (STRING_TO_REPLACE, Pattern)VALUES            (' ', '% %')                 ,('/', '%/%')                 ,('_', '%[_]%') ;CREATE TABLE #temp2(    STRING_NAME NVARCHAR(10));INSERT INTO #temp2 (STRING_NAME)VALUES            ('A BC')                 ,('D/EF_F E')                 ,('G_HI')                 ,('XYZ');WITH CTE_Replace AS(    SELECT   STRING_NAME AS OriginalString            ,CAST(STRING_NAME AS NVARCHAR(10)) AS ReplacedString            ,CAST('' AS NVARCHAR(10)) AS StringToReplace            ,1 AS ReplaceCount    FROM    #temp2 ancor    UNION ALL    SELECT   CTE_Replace.OriginalString            ,CAST(REPLACE(CTE_Replace.ReplacedString, rep.STRING_TO_REPLACE, '') AS NVARCHAR(10)) AS ReplacedString             ,CAST(rep.STRING_TO_REPLACE AS NVARCHAR(10)) AS StringToReplace            ,CTE_Replace.ReplaceCount + 1 AS ReplaceCount    FROM    #temp rep    INNER JOIN CTE_Replace ON CTE_Replace.ReplacedString LIKE rep.Pattern),CTE_FinalReplacedString AS(    SELECT  OriginalString            ,ReplacedString            ,ReplaceCount            ,ROW_NUMBER() OVER (PARTITION BY OriginalString ORDER BY ReplaceCount DESC) AS [Rank]    FROM    CTE_Replace)SELECT *FROM    CTE_FinalReplacedStringWHERE   [Rank] = 1

Note that #temp table was updated to include an extra column called Pattern, this column contains the search pattern to use in order to find the specific strings that has to be replaced. This was also done to simplify the join statement in the recursive CTE. Also note that in order to find the _ character the search pattern had to be updated as '%[_]%'. The reason for this is because SQL Server will interpret the _ character as a wild character instead of a specific character we are trying to find.

replace in the table is probably easier here

update t2 set t2.string_name = Replace(t2.string_name, t1.string_to_replace, '')from       #temp2 t2 cross join #temp1 t1