SQL Server remplacement multiple avec table #temporaire

Question

Je cherche à REMPLACER plusieurs caractères dans une requête SQL Server et je veux y parvenir via une table temporaire #temp au lieu d'une instruction REPLACE imbriquée. J'ai le code SQL ci-dessous et je veux obtenir un résultat comme

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

Je peux obtenir le résultat avec un remplacement imbriqué

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

mais j'aimerais vraiment faire cela avec une table temporaire #temp. Quelqu'un pourrait-il m'aider sur cela. Lorsque j'essaie d'exécuter mon premier code, j'obtiens l'erreur suivante

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

Voici une façon d'utiliser 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 

Résultat :

résultat
-----
ABC
DEF
GHI

Note : Cela ne fonctionnera que si chaque string_name a seulement un string_to_replace

Mise à jour : Pour gérer plus d'un string_to_replace dans un seul string_name, voici une façon d'utiliser Dynamic sql

J'ai apporté un petit changement à la table #temp en ajoutant une propriété identity pour boucler

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) 

Résultat :

Résultat
------
ABC
DEF
GHI
ABD

Answer 2

La possibilité de remplacer plusieurs éléments peut être réalisée via une CTE récursive comme le montre l'exemple suivant :

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

Remarquez que la table #temp a été mise à jour pour inclure une colonne supplémentaire appelée Pattern, cette colonne contient le motif de recherche à utiliser pour trouver les chaînes spécifiques à remplacer. Cela a également été fait pour simplifier l'instruction JOIN dans la CTE récursive. Notez également que pour trouver le caractère _, le motif de recherche a dû être mis à jour en tant que '%[_]%'',. La raison en est que SQL Server interprétera le caractère `_` comme un caractère générique au lieu d'un caractère spécifique que nous essayons de trouver.

Answer 3

Remplacer dans le tableau est probablement plus facile ici

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

Answer 4

Une autre façon avec une CTE récursive (batch complet ci-dessous) :

--Créer une table d'exemple, vous devriez utiliser YourTable
CREATE TABLE #temp2 (
    STRING_NAME NVARCHAR(max)
)

INSERT INTO #temp2 (STRING_NAME)
VALUES ('A BC'),('D/EF'),('G_HI'),('J_K/L_'),('MNO')
--J'ajoute quelques objets ici

La requête principale :

;WITH remplacement AS (
SELECT *
FROM (VALUES (' '),('/'),('_')
) as t(STRING_TO_REPLACE)
), cte AS (
SELECT  STRING_NAME,
        STRING_NAME as OriginalString,
        ROW_NUMBER() OVER (ORDER BY STRING_NAME) as rn,
        1 as [Level]
FROM #temp2 t2
UNION ALL
SELECT  REPLACE(c.STRING_NAME,t.STRING_TO_REPLACE,'~'),
        c.OriginalString,
        c.rn,
        [Level]+1
FROM cte c
INNER JOIN remplacement t
    ON CHARINDEX(t.STRING_TO_REPLACE,c.STRING_NAME,0) > 0
)

SELECT TOP 1 WITH TIES  OriginalString,
                        STRING_NAME
FROM cte 
ORDER BY ROW_NUMBER() OVER (PARTITION BY rn ORDER BY [Level] DESC)
OPTION (MAXRECURSION 0)

Résultat :

OriginalString  STRING_NAME
A BC            A~BC
D/EF            D~EF
J_K/L_          J~K~L~
G_HI            G~HI
MNO             MNO

Answer 5

Une façon simple de gérer cela est de télécharger une copie de PatExclude8K, une fonction T-SQL conçue spécifiquement pour ce type de tâche. Voici quelques exemples :

-- Supprimer tous les caractères non alphabétiques
SELECT NewString FROM #temp2 CROSS APPLY dbo.PatExclude8K(STRING_NAME,'[^A-Z]');
-- Supprimer tous les espaces, les barres obliques et les traits de soulignement
SELECT NewString FROM #temp2 CROSS APPLY dbo.PatExclude8K(STRING_NAME,'[ /_]');

Les deux requêtes produisent ce jeu de résultats :

NewString
------------
ABC
DEF
GHI