在单元格中的SQL Server删除重复值[关闭](Remove duplicate values

如何删除在T-SQL以下情况下重复？

我有一列的表Code型varchar(max) 。

它包含的值等A/A/B/C 。 我需要的单元格的值是A/B/C 。

另一种可能性是A/B/C/A需要被A/B/C

谢谢

Answer 1:

尝试这个。

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')

OUTPUT： B/A/C

如果你想删除重复，如果不有关订单护理那就试试这个。

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) 

OUTPUT： A/B/C

Answer 2:

我使用的打击代码从这个临客[SQL SERVER -删除重复项从逗号分隔的字符串] http://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-逗号分隔的字符串-UDF / ）

CREATE FUNCTION dbo.DistinctList
(
    @List VARCHAR(MAX),
    @Delim CHAR
)
RETURNS
    VARCHAR(MAX)
AS
BEGIN
    DECLARE @ParsedList TABLE
    (
    Item VARCHAR(MAX)
    )
    DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
    SET @list = LTRIM(RTRIM(@list)) + @Delim
    SET @pos = CHARINDEX(@delim, @list, 1)
    WHILE @pos > 0
    BEGIN
        SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
        IF @list1 <> ''
        INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
        SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
        SET @pos = CHARINDEX(@delim, @list, 1)
    END
    SELECT @rlist = COALESCE(@rlist+@Delim,'') + item
    FROM (SELECT DISTINCT Item FROM @ParsedList) t
    RETURN @rlist
END
GO

我改变了SELECT @rlist = COALESCE(@rlist+',','') + item来SELECT @rlist = COALESCE(@rlist+@Delim,'') + item因为原来的代码与逗号返回字符串。

如何使用：

SELECT dbo.DistinctList('A/B/C/A/A/A','/') DistinctList

文章来源: Remove duplicate values in a cell SQL Server [closed]