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.

Answer 1

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,2022

With an older version you could  likely take advantage of XML support or if really need to process this string yourself.

Answer 2

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

Answer 3

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 

Answer 4

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.