Replace values in a CSV string

I have a list of products in comma separated fashion and since the item list was replaced with new product items, I am trying to modify this CSV list with new product item list.

create table #tmp 
(
  id    int identity(1,1) not null,
  plist varchar(max) null
);

create table #tmpprod 
(
  oldid int null,
  newid int null
);

insert into #tmp(plist) values
('10,11,15,17,19'),
('22,34,44,25'),
('5,6,8,9');

insert into #tmpprod(oldid, newid) values
(5,  109),
(9,  110),
(10, 111),
(15, 112),
(19, 113),
(30, 114),
(34, 222),
(44, 333);

I am trying to use a split fn to convert into rows and then replace these values and then convert columns to rows again. Is it possible in any other manner?

The output will be as:

Solution 1:

Convert your comma separated list to XML. Use a numbers table, XQuery and position() to get the separate ID's with the position they have in the string. Build the comma separated string using the for xml path('') trick with a left outer join to #tempprod and order by position().

;with C as
(
  select T.id,
         N.number as Pos,
         X.PList.value('(/i[position()=sql:column("N.Number")])[1]', 'int') as PID
  from @tmp as T
    cross apply (select cast('<i>'+replace(plist, ',', '</i><i>')+'</i>' as xml)) as X(PList)
    inner join master..spt_values as N
      on N.number between 1 and X.PList.value('count(/i)', 'int')
  where N.type = 'P'  
)
select C1.id,
       stuff((select ','+cast(coalesce(T.newid, C2.PID) as varchar(10))
              from C as C2
                left outer join @tmpprod as T
                  on C2.PID = T.oldid
              where C1.id = C2.id
              order by C2.Pos
              for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '')
              
from C as C1
group by C1.id

Try on SE-Data

Solution 2:

Assuming SQL Server 2005 or better, and assuming order isn't important, then given this split function:

CREATE FUNCTION [dbo].[SplitInts]
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN ( SELECT Item FROM ( SELECT Item = x.i.value('(./text())[1]', 'int') 
    FROM 
    ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') 
              + '</i>').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i)
          ) AS y WHERE Item IS NOT NULL);
GO

You can get this result in the following way:

;WITH x AS
(
    SELECT id, item, oldid, [newid], rn = ROW_NUMBER() OVER
    (PARTITION BY id 
     ORDER BY PATINDEX('%,' + RTRIM(s.Item) + ',%', ',' + t.plist + ','))
    FROM #tmp AS t CROSS APPLY dbo.SplitInts(t.plist, ',') AS s
    LEFT OUTER JOIN #tmpprod AS p ON p.oldid = s.Item
)
SELECT id, newlist = STUFF((SELECT ',' + RTRIM(COALESCE([newid], Item)) 
    FROM x AS x2 WHERE x2.id = x.id
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 1, '') 
FROM x GROUP BY id;

Results:

Note that the ROW_NUMBER() / OVER / PARTITION BY / ORDER BY is only there to try to coerce the optimizer to return the rows in that order. You may observe this behavior today and it can change tomorrow depending on statistics or data changes, optimizer changes (service packs, CUs, upgrade, etc.) or other variables.

Long story short: if you're depending on that order, just send the set back to the client, and have the client construct the comma-delimited list. It's probably where this functionality belongs anyway.

That said, in SQL Server 2017+, we can guarantee retaining the order by splitting with OPENJSON() and reassembling with STRING_AGG():

;WITH x AS 
(
  SELECT o.id, val = COALESCE(n.newid, p.value), p.[key] 
  FROM #tmp AS o CROSS APPLY 
    OPENJSON('["' + REPLACE(o.pList, ',', '","') + '"]') AS p
  LEFT OUTER JOIN #tmpprod AS n 
  ON p.value = n.oldid
)
SELECT id, newlist = STRING_AGG(val, ',')
  WITHIN GROUP (ORDER BY [key])
  FROM x GROUP BY id;

• Example db<>fiddle

Solution 3:

Thanks for this question - I've just learned something new. The following code is an adaptation of an article written by Rob Volk on exactly this topic. This is a very clever query! I won't copy all of the content down here. I have adapted it to create the results you're looking for in your example.

CREATE TABLE #nums (n INT)
DECLARE @i INT 
SET @i = 1
WHILE @i < 8000 
BEGIN
    INSERT #nums VALUES(@i)
    SET @i = @i + 1
END


CREATE TABLE #tmp (
  id INT IDENTITY(1,1) not null,
  plist VARCHAR(MAX) null
)

INSERT INTO #tmp
VALUES('10,11,15,17,19'),('22,34,44,25'),('5,6,8,9')

CREATE TABLE #tmpprod (
  oldid INT NULL,
  newid INT NULL
)

INSERT INTO #tmpprod VALUES(5, 109),(9, 110),(10, 111),(15, 112),(19, 113),(30, 114),(34, 222),(44, 333)

;WITH cte AS (SELECT ID, NULLIF(SUBSTRING(',' + plist + ',' , n , CHARINDEX(',' , ',' + plist + ',' , n) - n) , '') AS prod
    FROM #nums, #tmp
    WHERE ID <= LEN(',' + plist + ',') AND SUBSTRING(',' + plist + ',' , n - 1, 1) = ',' 
    AND CHARINDEX(',' , ',' + plist + ',' , n) - n > 0)
UPDATE t SET plist = (SELECT CAST(CASE WHEN tp.oldid IS NULL THEN cte.prod ELSE tp.newid END AS VARCHAR) + ',' 
            FROM cte LEFT JOIN #tmpprod tp ON cte.prod = tp.oldid
            WHERE cte.id = t.id FOR XML PATH(''))
FROM #tmp t WHERE id = t.id

UPDATE #tmp SET plist = SUBSTRING(plist, 1, LEN(plist) -1)
WHERE LEN(plist) > 0 AND SUBSTRING(plist, LEN(plist), 1) = ','

SELECT * FROM #tmp
DROP TABLE #tmp
DROP TABLE #tmpprod
DROP TABLE #nums

The #nums table is a table of sequential integers, the length of which must be greater than the longest CSV you have in your table. The first 8 lines of the script create this table and populate it. Then I've copied in your code, followed by the meat of this query - the very clever single-query parser, described in more detail in the article pointed to above. The common table expression (WITH cte...) does the parsing, and the update script recompiles the results into CSV and updates #tmp.