In this tutorial, let me show you how to remove data from string in which data are separated by delimiter. An identifier that complies with all the rules for the format of identifiers can be used with or without delimiters. An identifier that does not comply with the rules for the format of regular identifiers must always be delimited.

Delimited identifiers are used in these situations:

  • When reserved words are used for object names or portions of object names.

    It is recommended that reserved keywords not be used as object names. Databases upgraded from earlier versions of Microsoft® SQL Server™ may contain identifiers that include words not reserved in the earlier version, but are reserved words for SQL Server 2000. You can refer to the object using delimited identifiers until the name can be changed.

  • When using characters not listed as qualified identifiers.

    SQL Server allows any character in the current code page to be used in a delimited identifier; however, indiscriminate use of special characters in an object name may make SQL statements and scripts difficult to read and maintain.

You can use the following code:   

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.
    DECLARE @String [varchar](max); 
    SET @String='10,11,12,10,11'; 
    SELECT dbo.DistinctList(@String,',') AS List; 


And here is the output:
10,11,12

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.