We can use below function to remove duplicate data from string .
- CREATE FUNCTION [dbo].[DistinctList]
- (
- @List VARCHAR(MAX),
- @Delim CHAR
- )
- RETURNS
- VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @Return_List [varchar](max);
- DECLARE @Temp_Str [varchar](max);
- DECLARE @Char_index int;
- SET @List=@List+@Delim;
- SET @Return_List='';
- SET @Char_index=CHARINDEX(@Delim,@List,1);
- WHILE @Char_index>0
- BEGIN
- SET @Temp_Str=SUBSTRING(@List,1,@Char_index-1);
- SET @Return_List=@Return_List+@Temp_Str+@Delim;
- SET @List=REPLACE(@List,@Temp_Str+@Delim,'');
- SET @Char_index=CHARINDEX(@Delim,@List,1);
- END
- Return SUBSTRING(@Return_List,1 ,LEN(@Return_List)-1);
- END
In this function first parameter take the string and second parameter the delimiter ,on the behalf of this delimiter we split the string and remove the duplicate data.
Example:
- DECLARE @String [varchar](max);
- SET @String='10,11,12,10,11';
- SELECT dbo.DistinctList(@String,',') AS List;
Output:
10,11,12