与替换数据相关的 SQL Server 2008 R2 查询

Question

我有一个场景，我必须删除除 a 或 b 或 c 之外的所有字符串

我的示例表如下：

Id     Product
------------------
1.     a,b,Da,c
2.     Ty,a,b,c
3.     a,sds,b

示例输出

Id    Product
----------------
1.     a,b,c
2.     a,b,c
3.     a,b

我当前的版本是Microsoft SQL Server 2008 R2

Answer 1

这应该对您有所帮助。正如我在评论中所说，我使用了Jeff Moden的DelimitSplit8k，因为你使用的是旧版本的SQL Server。如果您使用的是 2016+，您将可以访问STRING_SPLIT.我也规范化你的数据;因为存储分隔数据几乎总是一个坏主意。

CREATE TABLE #Sample (id int, Product varchar(20));
INSERT INTO #Sample
VALUES (1,'a,b,Da,c'),
(2,'Ty,a,b,c'),
(3,'a,sds,b');
GO
--The first problem you have is you're storing delimited data
--You really should be storing each item on a separate row.
--This is, however, quite easy to do. i'm going to use a different
--table, however, you can change this fairly easily for your
--needs.
CREATE TABLE #Sample2  (id int, Product varchar(2));
GO
--You can split the data out by using a Splitter.
--My personal preference is Jeff Moden's DelimitedSplit8K
--which I've linked to above.
INSERT INTO #Sample2 (id, Product)
SELECT id, Item AS Product
FROM #Sample S
CROSS APPLY dbo.DelimitedSplit8K(S.Product,',') DS
WHERE DS.Item IN ('a','b','c');
GO
--And hey presto! Your normalised data, and without the unwanted values
SELECT *
FROM #Sample2;
GO
DROP TABLE #Sample;
DROP TABLE #Sample2;

如果必须保留分隔格式，可以使用STUFF和FOR XML PATH：

WITH Split AS(
SELECT id,
Item AS Product,
ItemNumber
FROM #Sample S
CROSS APPLY dbo.DelimitedSplit8K(S.Product,',') DS
WHERE DS.Item IN ('a','b','c'))
SELECT id,
STUFF((SELECT ',' + Product
FROM Split sq
WHERE sq.id = S.id
ORDER BY ItemNumber
FOR XML PATH('')),1,1,'')
FROM Split S
GROUP BY id;

Answer 2

这也将只使用 xml 来做这件事：

select * into #t from (values('a,b,Da,c'),('Ty,a,b,c'),('a,sds,b'))v(Product)
;
with  x as (
SELECT t.Product, st.sProduct
FROM #t t
cross apply (
SELECT CAST(N'<root><r>' + REPLACE(t.Product,',', N'</r><r>') + N'</r></root>' as xml) xProduct
)xt
cross apply (
select CAST(r.value('.','NVARCHAR(MAX)') as nvarchar) sProduct
from xt.xProduct.nodes(N'//root/r') AS RECORDS(r)
) st
where st.sProduct in ('a', 'b', 'c')
)
select distinct x.Product, REVERSE(SUBSTRING(REVERSE(cleared.cProduct), 2, 999)) cleared
from x
cross apply ( select (
select distinct ref.sProduct + ','
from x ref
where ref.Product = x.Product
for xml path('') )
)cleared(cProduct)
;
drop table #t