查找和删除重复的子字符串 [英] 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屋！