删除单元格中的重复值 SQL Server

问题描述：

如何在以下情况下在 T-SQL 中删除重复项?

How do I remove duplicates in the following case in T-SQL?

我有一个表，其中包含 varchar(max) 类型的 Code 列.

I have a table with a column Code of type varchar(max).

它包含一个类似于 A/A/B/C 的值.我需要单元格值为 A/B/C.

It contains a value like A/A/B/C. I need the cell value to be A/B/C.

其他可能是A/B/C/A需要是A/B/C

谢谢

答

试试这个.

CREATE FUNCTION STR_Func (@Str AS VARCHAR(100))
RETURNS VARCHAR(Max)
AS
  BEGIN
      DECLARE @count   INT,
              @tempstr VARCHAR(100)='',
              @nLength INT,
              @outstr  VARCHAR(100)=''

      SET @count=1
      SET @Str = Replace(@Str, '/', '')
      SET @nlength = Len(@Str)

      WHILE ( @count < @nLength )
        BEGIN
            SET @tempstr=@tempstr + Substring(@Str, 1, 1)
            SET @Str=Replace(@Str, Substring(@Str, 1, 1), '')
            SELECT @outstr = @outstr + RIGHT(@tempstr, 1) + '/'
            SET @count=@count + 1
        END

      RETURN LEFT(@outstr, Len(@outstr) - 1)
  END 

select dbo.STR_Func('B/A/C/A')

输出: B/A/C

如果您想删除重复项并且不关心订单，请尝试此操作.

If you want to remove duplicates and if dont care about the order then try this.

DECLARE @code  VARCHAR(100)='B/A/C/A',
        @code1 VARCHAR(100)=''


SELECT @code1 += '/' + splitrow
FROM   (SELECT DISTINCT Split.a.value('.', 'VARCHAR(100)') splitrow
        FROM   (SELECT Cast ('<M>' + Replace(@code, '/', '</M><M>') + '</M>' AS XML) AS Data) AS A
               CROSS APPLY Data.nodes ('/M') AS Split(a)) b

SELECT RIGHT(@code1, Len(@code1) - 1)

输出:A/B/C

删除单元格中的重复值 SQL Server

相关推荐