替换 CSV 字符串中的值 [英] Replace values in a CSV string

问题描述

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

创建表#tmp (id int identity(1,1) 不为空，plist varchar(max) null)创建表#tmpprod (oldid int null，newid int null)插入#tmp选择10、11、15、17、19"联盟选择'22,34,44,25'联盟选择'5,6,8,9'插入#tmpprod选择 5, 109联盟选择 9, 110联盟选择 10, 111联盟选择 15, 112联盟选择 19, 113联盟选择 30, 114联盟选择 34, 222联盟选择 44, 333删除表#tmp删除表#tmpprod

我正在尝试使用拆分 fn 转换为行，然后替换这些值，然后再次将列转换为行.是否有可能以任何其他方式?

输出如下:

<前>1 111,11,112,17,1132 22,222,333,253 109,6,8,110

解决方案

将逗号分隔的列表转换为 XML.使用数字表、XQuery 和 position() 来获取单独的 ID，以及它们在字符串中的位置.使用 for xml path('') 技巧和 left outer join 到 #tempprod 并按 排序构建逗号分隔的字符串position().

;以C为(选择T.id，N.number as Pos,X.PList.value('(/i[position()=sql:column(N.Number")])[1]', 'int') 作为PID来自 @tmp 作为 T交叉应用 (select cast('<i>'+replace(plist, ',', '</i><i>')+'</i>' as xml)) 作为 X(PList)内连接 master..spt_values 为 N在 1 和 X.PList.value('count(/i)', 'int') 之间的 N.number其中 N.type = 'P')选择 C1.id，东西((选择'，'+cast(coalesce(T.newid，C2.PID)作为varchar(10))从 C 作为 C2左外连接 @tmpprod 作为 T在 C2.PID = T.oldid其中 C1.id = C2.id通过 C2.Pos 订购对于 xml path(''), type).value('.', 'varchar(max)'), 1, 1, '')从 C 作为 C1按 C1.id 分组

尝试 SE-Data

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
select '10,11,15,17,19'
union
select '22,34,44,25'
union
select '5,6,8,9'

insert into #tmpprod
select 5, 109
union
select 9, 110
union
select 10, 111
union
select 15, 112
union
select 19, 113
union
select 30, 114
union
select 34, 222
union
select 44, 333

drop table #tmp
drop table #tmpprod

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:

1   111,11,112,17,113
2   22,222,333,25
3   109,6,8,110

解决方案

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

这篇关于替换 CSV 字符串中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！