locked
How to remove duplicate string values in SQL RRS feed

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

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

    With 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