SQL Server multiple REPLACE with #temp table

Question

I am trying to REPLACE multiple characters in SQL Server query and want to achieve this via #temp table instead of nested REPLACE. I got SQL code below and want to achieve result like

ABC
DEF
GHI

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
(
    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')

SELECT REPLACE(t2.STRING_NAME,(SELECT t1.STRING_TO_REPLACE   
                               FROM #temp t1),'') 
 FROM #temp2 t2

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL DROP TABLE #temp2

I can achieve result with nested replace

SELECT REPLACE(REPLACE(REPLACE(t2.STRING_NAME,'_',''),'/',''),' ','')  FROM #temp2 t2

but would really like to do this via #temp table. Please can someone help me on this. When I try to run my first code I get the following error

Msg 512, Level 16, State 1, Line 23 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,

, >= or when the subquery is used as an expression.

Answer 1

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
-----
ABC
DEF
GHI

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
------
ABC
DEF
GHI
ABD

Answer 2

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

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
(
    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_FinalReplacedString
WHERE   [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.