删除字段中内部以空格分隔的部分

Question

我在SQL Server 2008数据库中有大量行

对于每一行，我有3列我关心

一个典型的行如下所示：

AccountNumber    | basecode                      | subcode
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
9689787209368901 | AQTXG AQTXG AQTXG AQTXG ACC5Z | ZQ596 ZQ596 ZQ596 ZQ655 ZC655

某些基代码需要连同其相应的子代码一起删除。basecode和supcode都是由空格分隔的有序列表。我需要一个查询，输入基本代码，它会删除基本代码和子代码，并保持列表的其余部分不变。

每一行都有相同数量的基代码和子代码项。代码的数量可以在0到45之间变化。同一个基本代码可以出现多次。所有字段都是varchars。

因此，数据最终(动态解析)如下：(因为每个账户有可变数量的基码/子码(对)。

如果我试图删除"ACC5Z"，那么我应该有

AccountNumber    | basecode                | subcode
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
9689787209368901 | AQTXG AQTXG AQTXG AQTXG | ZQ596 ZQ596 ZQ596 ZQ655

如果我试图删除"AQTXG"，那么我应该有

AccountNumber    | basecode   | subcode
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
9689787209368901 | ACC5Z      | ZC655

Answer 1

试试这个

DECLARE @t TABLE (AccountNumber VARCHAR(100),Basecode VARCHAR(100),Subcode VARCHAR(100))
INSERT INTO @t SELECT 9689787209368901,'AQTXG AQTXG AQTXG AQTXG ACC5Z','ZQ596 ZQ596 ZQ596 ZQ655 ZC655'
DECLARE @BaseCodetoRemove VARCHAR(10) = 'AQTXG'
;WITH CTE AS(
SELECT  
AccountNumber
,LTRIM(RTRIM(SUBSTRING(Basecode, Number ,CHARINDEX(' ', Basecode + ' ', Number ) - Number))) AS Basecode
, LTRIM(RTRIM(SUBSTRING(Subcode, Number ,CHARINDEX(' ', Subcode + ' ', Number ) - Number))) AS Subcode
FROM @t 
JOIN master..spt_values ON Number <= DATALENGTH(Basecode) + 1  AND type='P'
AND SUBSTRING(' ' + Basecode, Number , 1) = ' ')
,CTE2 AS(
SELECT * 
FROM CTE 
WHERE BaseCode <> @BaseCodetoRemove)
SELECT AccountNumber
,STUFF((SELECT  ' ' + CAST(Basecode AS vARCHAR(MAX))
FROM CTE2 t2 WHERE t1.AccountNumber = t2.AccountNumber
FOR XML PATH('')),1,1,'')Basecode
,STUFF((SELECT ' ' + CAST(Subcode AS vARCHAR(MAX))
FROM CTE2 t2 WHERE t1.AccountNumber = t2.AccountNumber
FOR XML PATH('')),1,1,'')Subcode
FROM CTE2  t1
GROUP BY t1.AccountNumber