查找和删除重复的子字符串 [英] Find and Remove Repeated Substrings
本文介绍了查找和删除重复的子字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 SQL Server 2008 表中有一个列,其中部分字符串被意外重复.
I've a column in a SQL Server 2008 table where part of the string was accidentally repeated.
有没有人有一种快速简便的方法来删除尾随重复的子字符串?
Does anyone have a quick and easy way to remove the trailing duplicated substring?
例如
alpha\bravo\charlie\delta\charlie\delta
应该
alpha\bravo\charlie\delta
推荐答案
如果您还没有数字表:
SET NOCOUNT ON;
DECLARE @UpperLimit INT;
SET @UpperLimit = 4000;
WITH n(rn) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.all_columns
)
SELECT [Number] = rn - 1
INTO dbo.Numbers FROM n
WHERE rn <= @UpperLimit + 1;
CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);
现在是一个通用的拆分函数,它将把你的分隔字符串变成一个集合:
Now a generic split function, that will turn your delimited string into a set:
CREATE FUNCTION dbo.SplitString
(
@List NVARCHAR(MAX),
@Delim CHAR(1)
)
RETURNS TABLE
AS
RETURN ( SELECT
rn,
vn = ROW_NUMBER() OVER (PARTITION BY [Value] ORDER BY rn),
[Value]
FROM
(
SELECT
rn = ROW_NUMBER() OVER (ORDER BY CHARINDEX(@Delim, @List + @Delim)),
[Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM dbo.Numbers
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], 1) = @Delim
) AS x
);
GO
然后是一个将它们重新组合在一起的函数:
And then a function that puts them back together:
CREATE FUNCTION dbo.DedupeString
(
@List NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN ( SELECT newval = STUFF((
SELECT '\' + x.[Value] FROM dbo.SplitString(@List, '\') AS x
WHERE (x.vn = 1)
ORDER BY x.rn
FOR XML PATH, TYPE).value('.', 'nvarchar(max)'), 1, 1, '')
);
END
GO
示例用法:
SELECT dbo.DedupeString('alpha\bravo\bravo\charlie\delta\bravo\charlie\delta');
结果:
alpha\bravo\charlie\delta
你也可以这样说:
UPDATE dbo.MessedUpTable
SET OopsColumn = dbo.DedupeString(OopsColumn);
@MikaelEriksson 可能会以一种更有效的方式使用 XML 来消除重复,但这就是我在此之前可以提供的.:-)
@MikaelEriksson will probably swoop in with a more efficient way to use XML to eliminate duplicates, but that is what I can offer until then. :-)
这篇关于查找和删除重复的子字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文