如何从SQL中的字符串中删除重复项

如何从SQL中的字符串中删除重复项

具有解析功能的选项1

Declare @YourTable table (ID int,YourCol varchar(50))Insert Into @YourTable values(1,'qw"qw"er"er'),(2,'q"w"w"q'),(3,'f"k"s"g')Select A.ID      ,A.YourCol      ,DeDuped   = Stuff((Select '"' + RetVal      From (Select RetSeq=Min(RetSeq),RetVal  From  [dbo].[udf-Str-Parse](A.YourCol,'"')  Group By RetVal) P        Order by 1 For XML Path('')),1,1,'')  From  @YourTable A

退货

ID  YourCol      DeDuped1   qw"qw"er"er  qw"er2   q"w"w"q      q"w3   f"k"s"g      f"k"s"g

选项2：不带解析功能

Declare @YourTable table (ID int,YourCol varchar(50))Insert Into @YourTable values(1,'qw"qw"er"er'),(2,'q"w"w"q'),(3,'f"k"s"g')Select A.ID      ,A.YourCol      ,DeDuped   = Stuff((Select '"' + RetVal      From (Select RetSeq=Min(RetSeq),RetVal  From  (Select RetSeq = Row_Number() over (Order By (Select null))      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))From  (Select x = Cast('<x>' + replace((Select replace(A.YourCol,'"','搂搂Split搂搂') as [*] For XML Path('')),'搂搂Split搂搂','</x><x>')+'</x>' as xml).query('.')) as A Cross Apply x.nodes('x') AS B(i)       ) P1 Group By RetVal) P        Order by RetSeq       For XML Path('')),1,1,'')  From  @YourTable A

退货

ID  YourCol      DeDuped1   qw"qw"er"er  qw"er2   q"w"w"q      q"w3   f"k"s"g      f"k"s"g

UDF（如果有兴趣）

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))Returns Table AsReturn (      Select RetSeq = Row_Number() over (Order By (Select null))          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'搂搂Split搂搂') as [*] For XML Path('')),'搂搂Split搂搂','</x><x>')+'</x>' as xml).query('.')) as A     Cross Apply x.nodes('x') AS B(i));--Thanks Shnugo for making this XML safe--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')--Performance On a 5,000 random sample -8K 77.8ms, -1M 79ms (+1.16), -- 91.66ms (+13.8)