从字符串中删除“1”和“2”,而不删除“11”和“12”

正确的做法是停止使用分隔字符串。

有关更多信息,请阅读 Is storing a delimited list in a database column really that bad? ,你会看到这个问题的答案是 绝对可以!

然而,我知道有时更改数据库结构是不可能的,即使这是正确的做法,所以解决方法是在的第一个参数中包括分隔符 replace 然后把它修剪掉。

首先,创建并填充示例表( 请 请在以后的问题中省去这一步):

CREATE TABLE TableName (
  Id int identity(1,1) ,
  DelimitedValues varchar(100)
);

INSERT INTO TableName (DelimitedValues) VALUES
('1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15'), 
('2, 3, 1, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15'), 
('1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15');

更新声明:

UPDATE TableName
SET DelimitedValues = TRIM(', ' FROM 
  REPLACE(', '+ DelimitedValues +', ', ', 1, ', ', '))
WHERE Id < 3;

快速选择以确保更新成功:

SELECT Id, DelimitedValues
FROM TableName

结果:

你可以在上看到现场演示 db<>fiddle

正如我在评论中提到的,真正的解决方案是修复你的设计。

让我们创建一个快速示例数据集:

CREATE TABLE dbo.YourTable(ID int IDENTITY(1,1) CONSTRAINT PK_YourTable PRIMARY KEY,
                           SomeInt int NOT NULL,
                           SomeDate date NULL,
                           YourDenormalisedColumn varchar(8000) NOT NULL);
GO

INSERT INTO dbo.YourTable (SomeInt,
                           SomeDate,
                           YourDenormalisedColumn)
VALUES(24,GETDATE(),'1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15'),
      (117,NULL,'2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15'),
      (3,'20231117','1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15');

创建和 INSERT ing数据实际上很简单,我们只是 CREATE 我们需要并使用的表的最小细节 STRING_SPLIT 以插入新数据。那我们可以 DROP 旧的非标准化列:

CREATE TABLE dbo.NormalisedData (ArbitraryID int IDENTITY CONSTRAINT PK_NormalisedData PRIMARY KEY,
                                 YourID int NOT NULL CONSTRAINT DF_NormalisedData_YourTable FOREIGN KEY REFERENCES dbo.YourTable(ID),
                                 NormalisedValue int NOT NULL);
GO

INSERT INTO dbo.NormalisedData (YourID,
                                NormalisedValue)
SELECT YT.ID,
       SS.value
FROM dbo.YourTable YT
     CROSS APPLY STRING_SPLIT(YT.YourDenormalisedColumn,',') SS;
GO

ALTER TABLE dbo.YourTable DROP COLUMN YourDenormalisedColumn;

现在我们有了一个标准化的数据集,您的需求很简单,它只是一个简单的 DELETE :

DELETE FROM dbo.NormalisedData
WHERE NormalisedValue IN (1,2);

如果你坚持不能改变你的设计(你可以、应该、必须),那么你 能够 拆分字符串,删除“有问题”的值,然后重新聚合:

--Run before the COLUMN is DROPed
UPDATE YT
SET YT.YourDenormalisedColumn = (SELECT STRING_AGG(SS.value,', ') WITHIN GROUP (ORDER BY ss.ordinal)
                                 FROM STRING_SPLIT(YT.YourDenormalisedColumn,',',1) SS
                                 WHERE TRIM(ss.value) NOT IN ('1','2')) --TRIM because you have leading spaces and ' 1' <> '1'
FROM dbo.YourTable YT;

--Clean up
DROP TABLE dbo.NormalisedData;
DROP TABLE dbo.YourTable;

db<>fiddle

edit:在键入所有这些之后,我看到它与Thom A已经给出的答案相似,只是我使用CTE而不是创建表。

您可以使用STRING_SPLIT将csv字段转换为正确的数据,然后处理该数据,然后使用STRING_AGG将其取消转换为csv字段。

STRING_SPLIT在SQL Server 2016中引入
STRING_AGG在SQL Server 2017中引入

例如,如果您的密钥是MyPrimaryKey,并且具有CSV数据的字段称为CsvField,则可以按如下方式将其拆分:

select t.MyPrimaryKey, LTRIM(RTRIM(split.value)) as split_out
from MyTable t
    cross apply STRING_SPLIT(t.CsvField,',') as split

这将为每个值提供单独的行,如下所示:

MyPrimaryKey split_out
------------ ---------
one          2
one          3
one          4
one          5
one          6
two          1
two          2
     etc...

一旦你有了这种格式,你可以很容易地删除你不想要的值,使用这样的东西:

with cteStageOne as (
    select t.MyPrimaryKey, LTRIM(RTRIM(split.value)) as split_out
    from MyTable t
        cross apply STRING_SPLIT(t.CsvField,',') as split
)
select *
from cteStageOne
where split_out not in ('1','2')

这里有一个完整的解决方案,可以将字符串重新聚合在一起:

with cteStageOne as (
    select t.MyPrimaryKey, LTRIM(RTRIM(split.value)) as split_out
    from MyTable t
        cross apply STRING_SPLIT(t.CsvField,',') as split
), cteStageTwo as (
    select MyPrimaryKey, split_out
    from cteStageOne
    where split_out not in ('1','2')
)
select MyPrimaryKey, STRING_AGG(split_out,', ') as recombine
from cteStageTwo
group by MyPrimaryKey

UPDATE your_table
SET your_column = TRIM(BOTH ',' FROM REGEXP_REPLACE(your_column, '\\b(1|2),\\s(?![0-9])', ''))