如何在 T-SQL 中删除以下情况下的重复项?
我有一个表,其中的列Code
类型为 varchar(max)
.
它包含一个值,如 A/A/B/C
.我需要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
如果您想删除重复项并且不关心顺序,请尝试此操作。
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 – 从逗号分隔字符串中删除重复条目]http://blog.sqlauthority.com/2009/01/15/sql-服务器-删除重复条目-从逗号分隔字符串-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