SQL Server 去掉数组项

declare @srctext nvarchar(max) = 'a,b,c';    
declare @deltext nvarchar(max)  = 'b';
declare @rtnText nvarchar(max);          
           
-- SQL Server 去掉数组项
SELECT @rtnText = STUFF(
    (SELECT ',' + value
     FROM STRING_SPLIT(@srctext, ',')
     WHERE value not like char(37)+@deltext+char(37)
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');    

--结果：a,c
select @rtnText; 

 

同理，查询字段中包含字符串

declare @srctext nvarchar(max) = 'a,b,c';    
declare @deltext nvarchar(max)  = 'b';
declare @rtnText nvarchar(max);          
           
-- SQL Server 去掉数组项
SELECT @rtnText = STUFF(
    (SELECT ',' + value
     FROM STRING_SPLIT(@srctext, ',')
     WHERE value like char(37)+@deltext+char(37)
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');    

--结果：b
select @rtnText;