替换 CSV 字符串中的值

我有一个以逗号分隔的产品列表，由于项目列表已替换为新产品项目，我正在尝试使用新产品项目列表修改此 CSV 列表。

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);

我正在尝试使用 split fn 转换为行，然后替换这些值，然后再次将列转换为行。是否可以通过其他方式实现？

输出将如下：

将逗号分隔列表转换为 XML。使用数字表、XQuery 和position()获取单独的 ID 及其在字符串中的位置。使用以下命令构建逗号分隔的字符串for xml path('')技巧与left outer join to #tempprod并排序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 https://data.stackexchange.com/stackoverflow/query/63267/replace-ids-in-comma-separated-list