Answered by:
How to remove duplicate string values in SQL

Question
-
User-582711651 posted
Hi Friends,
Ref URL https://blog.sqlauthority.com/2017/09/11/sql-server-remove-duplicate-chars-string-part-2/
Similar this,
Set @string = '2021,2022,121,121,151,161,121,1991,2020,121,161'
want to remove duplicates and expected results is ... (required ",")
'121,151,161,1991,2020,2021,2020'
Please help.
Thanks in advance.
Monday, September 7, 2020 3:04 PM
Answers
-
User753101303 posted
Hi,
Which version? You could use DISTINCT when processing that as a list (as it is a list). For example with 2016 or later:
CREATE FUNCTION DistinctValue(@value NVARCHAR(max)) RETURNS NVARCHAR(max)
AS BEGIN
SELECT @value=STRING_AGG(value,',') FROM (
SELECT DISTINCT value FROM STRING_SPLIT(@value,',')
) a
RETURN @value
END
GO
DECLARE @String NVARCHAR(max)
SET @string=dbo.DistinctValue('2021,2022,121,121,151,161,121,1991,2020,121,161')
PRINT @string -- shows 121,151,161,1991,2020,2021,2022With an older version you could likely take advantage of XML support or if really need to process this string yourself.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, September 7, 2020 4:00 PM -
User753101303 posted
Then hopefully using XML support which should be older:
CREATE FUNCTION DistinctValue(@value NVARCHAR(max)) RETURNS NVARCHAR(max) AS BEGIN DECLARE @xml XML -- turns the input string to XML SET @xml='<v>'+REPLACE(@value,',','</v><v>')+'</v>' -- turns back to a string SET @value=(SELECT ','+CAST(v AS VARCHAR) FROM ( -- distinct values from xml nodes SELECT DISTINCT v.value('.','nvarchar(max)') AS v FROM @xml.nodes('v') AS t(v) ) a FOR XML PATH('')) -- delete leading , SET @value=STUFF(@value,1,1,'') RETURN @value END GO DECLARE @String NVARCHAR(max) SET @string=dbo.DistinctValue('2021,2022,121,121,151,161,121,1991,2020,121,161') PRINT @string -- shows 121,151,161,1991,2020,2021,2022
If still older (knowing which version you are using could help) then your last resort will be to use string functions to build even more explicitly a table you could select disitnct from.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, September 7, 2020 9:04 PM
All replies
-
User753101303 posted
Hi,
Which version? You could use DISTINCT when processing that as a list (as it is a list). For example with 2016 or later:
CREATE FUNCTION DistinctValue(@value NVARCHAR(max)) RETURNS NVARCHAR(max)
AS BEGIN
SELECT @value=STRING_AGG(value,',') FROM (
SELECT DISTINCT value FROM STRING_SPLIT(@value,',')
) a
RETURN @value
END
GO
DECLARE @String NVARCHAR(max)
SET @string=dbo.DistinctValue('2021,2022,121,121,151,161,121,1991,2020,121,161')
PRINT @string -- shows 121,151,161,1991,2020,2021,2022With an older version you could likely take advantage of XML support or if really need to process this string yourself.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, September 7, 2020 4:00 PM -
User-582711651 posted
Hi,
SQL 2012 version, yes getting this err.
Msg 195, Level 15, State 10, Procedure DistinctValue, Line 4
'STRING_AGG' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Procedure DistinctValue, Line 6
Incorrect syntax near 'a'.Monday, September 7, 2020 4:43 PM -
User409696431 posted
You didn't answer what version you are using.
STRING_AGG applies to <token>SQL Server 2017 (14.x)</token> and later. See: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
Monday, September 7, 2020 8:31 PM -
User753101303 posted
Then hopefully using XML support which should be older:
CREATE FUNCTION DistinctValue(@value NVARCHAR(max)) RETURNS NVARCHAR(max) AS BEGIN DECLARE @xml XML -- turns the input string to XML SET @xml='<v>'+REPLACE(@value,',','</v><v>')+'</v>' -- turns back to a string SET @value=(SELECT ','+CAST(v AS VARCHAR) FROM ( -- distinct values from xml nodes SELECT DISTINCT v.value('.','nvarchar(max)') AS v FROM @xml.nodes('v') AS t(v) ) a FOR XML PATH('')) -- delete leading , SET @value=STUFF(@value,1,1,'') RETURN @value END GO DECLARE @String NVARCHAR(max) SET @string=dbo.DistinctValue('2021,2022,121,121,151,161,121,1991,2020,121,161') PRINT @string -- shows 121,151,161,1991,2020,2021,2022
If still older (knowing which version you are using could help) then your last resort will be to use string functions to build even more explicitly a table you could select disitnct from.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, September 7, 2020 9:04 PM