具有解析功能的选项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)
