如何从SQL中的字符串中删除重复项 [英] How to remove duplicates from a string in SQL
本文介绍了如何从SQL中的字符串中删除重复项的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在SQL表中有一列,其中一些值是用bij "
分隔的.此字符串中的某些值是重复的,我想删除.这是我的数据示例:
I got a column in my SQL table where some values are seperated bij "
.
Some values in this string are duplicated which I want to remove. Here is an example of my data:
---------------
| qw"qw"er"er |
---------------
| q"w"w"q |
---------------
| f"k"s"g |
---------------
现在,结果应替换所有重复项:
Now The result should replace any duplicates:
---------------
| qw"er |
---------------
| q"w" |
---------------
| f"k"s"g |
---------------
因此,首先我要分割字符串,然后删除重复项.有人可以帮我解决这个问题吗?
So first I want to split the string and then remove duplicates. Could anyone help me with this issue?
推荐答案
具有解析功能的选项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 DeDuped
1 qw"qw"er"er qw"er
2 q"w"w"q q"w
3 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 DeDuped
1 qw"qw"er"er qw"er
2 q"w"w"q q"w
3 f"k"s"g f"k"s"g
感兴趣的UDF
CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
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)
这篇关于如何从SQL中的字符串中删除重复项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文