Split string with delimiter

While developing any small, medium or large application, we always come to scenario where we need to split string with delimiter. Everyone having their own logic to implement this feature. Please find below 3 ways of implementation for this scenario.

1) Using while loop.

CREATE FUNCTION [dbo].[fn_split_string](
    @pInputList NVARCHAR(MAX),        -- List of delimited items
    @pDelimiter NVARCHAR(MAX) = ',' -- delimiter that separates items
) 
RETURNS @List TABLE(item NVARCHAR(MAX))
AS
/******************************************************************************************************************************************************      
Function Name        :  fn_split_string     
Created By            :  Asif Ghanchi
Date                :  10/03/2012
                      :   
Description            :  This function returns list list of delimited items.
                    :
                    : 
Procedure called from : Database      
-------------------------------------------------------------------------------------------------------------------------------------------------------      
Maintenance log      
-------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------------------------
MOD LOG:    
DEVELOPER    DATE        COMMENTS                                 
----------- ------------- -----------------------------------------------------------------------------------------------------------------------------      
*******************************************************************************************************************************************************      
** Sample of SP execution      
** ------------------------      
SELECT * FROM dbo.fn_split_string('1,2,3',',')
**
** Result:
** -----------------------------------------------------------------------------
item
1
2
3
** -----------------------------------------------------------------------------
** RUNTIME in DEV: (hh:min:ss): 00:00:00
** RUNTIME in QA:  (hh:min:ss): 00:00:00     
******************************************************************************************************************************************************/ 
BEGIN
    DECLARE 
        @vInput NVARCHAR(MAX),
        @vIndex INT,
        @vLengthOfDelimiter INT,
        @vLengthOfInputList INT
        
    SET @vLengthOfDelimiter = LEN(@pDelimiter)
    SET @vLengthOfInputList = LEN(@pInputList)    
    SET @vIndex = CHARINDEX(@pDelimiter,@pInputList,0)
    
    WHILE @vIndex <> 0
    BEGIN
        SET @vInput        = RTRIM(LTRIM(SUBSTRING(@pInputList,1,@vIndex - 1)))
        SET @pInputList = RTRIM(LTRIM(SUBSTRING(@pInputList,@vIndex + @vLengthOfDelimiter,@vLengthOfInputList)))
        
        INSERT INTO @List SELECT @vInput
        
        SET @vLengthOfInputList = LEN(@pInputList)    
        SET @vIndex = CHARINDEX(@pDelimiter,@pInputList,0)
    END

    INSERT INTO @List SELECT @pInputList -- Put the last item in
    
    RETURN
END

GO

2) Using XQuery

CREATE FUNCTION [dbo].[fn_split_string_update_1](
    @pInputList NVARCHAR(MAX),        -- List of delimited items
    @pDelimiter NVARCHAR(MAX) = ',' -- delimiter that separates items
) 
RETURNS @List TABLE(item NVARCHAR(MAX))
AS
/******************************************************************************************************************************************************      
Stored Procedure    :  fn_split_string_update_1     
Created By            :  Asif Ghanchi
Date                :  01/02/2012
                      :   
Description            :  This function returns list list of delimited items.
                    :
                    : 
Procedure called from : Database      
-------------------------------------------------------------------------------------------------------------------------------------------------------      
Maintenance log      
-------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------------------------
MOD LOG:    
DEVELOPER    DATE        COMMENTS                                 
----------- ------------- -----------------------------------------------------------------------------------------------------------------------------      
*******************************************************************************************************************************************************      
** Sample of SP execution      
** ------------------------      
SELECT * FROM dbo.fn_split_string_update_1('1,2,3',',')
**
** Result:
** -----------------------------------------------------------------------------
item
1
2
3
** -----------------------------------------------------------------------------
** RUNTIME in DEV: (hh:min:ss): 00:00:00
** RUNTIME in QA:  (hh:min:ss): 00:00:00     
******************************************************************************************************************************************************/ 
BEGIN
    DECLARE @vInputList XML = '<InputList><Input>'+ REPLACE(@pInputList,@pDelimiter,'</Input><Input>') +'</Input></InputList>'
    
    INSERT INTO @List
    SELECT
        LTRIM(RTRIM(CAST(IL.Col.query('data(.)') AS NVARCHAR)))
    FROM
        @vInputList.nodes('/InputList/Input') IL(Col);    
    RETURN
END
GO

3) We also can create function with different data type to increaes peformance. Below is one example for INT.

CREATE FUNCTION [dbo].[fn_split_string_INT](
    @pInputList NVARCHAR(MAX),        -- List of delimited items
    @pDelimiter NVARCHAR(MAX) = ',' -- delimiter that separates items
) 
RETURNS @List TABLE(item INT)
AS
/******************************************************************************************************************************************************      
Function Name        :  fn_split_string_INT     
Created By            :  Asif Ghanchi
Date                :  01/02/2012
                      :   
Description            :  This function returns list list of delimited items.
                    :    
                    : 
Procedure called from : Database      
-------------------------------------------------------------------------------------------------------------------------------------------------------      
Maintenance log      
-------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------------------------
MOD LOG:    
DEVELOPER    DATE        COMMENTS                                 
----------- ------------- -----------------------------------------------------------------------------------------------------------------------------      
*******************************************************************************************************************************************************      
** Sample of SP execution      
** ------------------------      
SELECT * FROM dbo.fn_split_string_INT('1,2,3',',')
**
** Result:
** -----------------------------------------------------------------------------
item
1
2
3
** -----------------------------------------------------------------------------
** RUNTIME in DEV: (hh:min:ss): 00:00:00
** RUNTIME in QA:  (hh:min:ss): 00:00:00     
******************************************************************************************************************************************************/ 
BEGIN
    DECLARE 
        @vInput INT,
        @vIndex INT,
        @vLengthOfDelimiter INT,
        @vLengthOfInputList INT
        
    SET @vLengthOfDelimiter = LEN(@pDelimiter)
    SET @vLengthOfInputList = LEN(@pInputList)    
    SET @vIndex = CHARINDEX(@pDelimiter,@pInputList,0)
    
    WHILE @vIndex <> 0
    BEGIN
        SET @vInput        = RTRIM(LTRIM(SUBSTRING(@pInputList,1,@vIndex - 1)))
        SET @pInputList = RTRIM(LTRIM(SUBSTRING(@pInputList,@vIndex + @vLengthOfDelimiter,@vLengthOfInputList)))
        
        INSERT INTO @List SELECT @vInput
        
        SET @vLengthOfInputList = LEN(@pInputList)    
        SET @vIndex = CHARINDEX(@pDelimiter,@pInputList,0)
    END

    INSERT INTO @List SELECT @pInputList -- Put the last item in
    
    RETURN
END
GO

4) Using CTE

CREATE FUNCTION [dbo].[fn_split_string_update_3](
    @pInputList NVARCHAR(MAX),        -- List of delimited items
    @pDelimiter NVARCHAR(MAX) = ',' -- delimiter that separates items
) 
RETURNS @List TABLE(item NVARCHAR(MAX))
AS
/******************************************************************************************************************************************************      
Function Name        :  fn_split_string_update_3     
Created By            :  Asif Ghanchi
Date                :  01/02/2012
                      :   
Description            :  This function returns list list of delimited items.
                    :    
                    : 
Procedure called from : Database      
-------------------------------------------------------------------------------------------------------------------------------------------------------      
Maintenance log      
-------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------------------------
MOD LOG:    
DEVELOPER    DATE        COMMENTS                                 
----------- ------------- -----------------------------------------------------------------------------------------------------------------------------      
*******************************************************************************************************************************************************      
** Sample of SP execution      
** ------------------------      
SELECT * FROM dbo.fn_split_string_update_3('1,2,3',',')
**
** Result:
** -----------------------------------------------------------------------------
item
1
2
3
** -----------------------------------------------------------------------------
** RUNTIME in DEV: (hh:min:ss): 00:00:00
** RUNTIME in QA:  (hh:min:ss): 00:00:00     
******************************************************************************************************************************************************/ 
BEGIN
    DECLARE 
        @vInput INT,
        @vCurrentIndex INT,
        @vNextIndex INT,
        @vLengthOfDelimiter INT,
        @vLengthOfInputList INT
        
    SET @vLengthOfDelimiter = LEN(@pDelimiter)
    SET @vLengthOfInputList = LEN(@pInputList)    
    SET @vCurrentIndex = CHARINDEX(@pDelimiter,@pInputList,1)
    SET @vNextIndex = CHARINDEX(@pDelimiter,@pInputList,@vCurrentIndex + @vLengthOfDelimiter)
    
    ;WITH InputList(Input,CurrentIndex,NextIndex)
    AS
    (
        SELECT 
            RTRIM(LTRIM(SUBSTRING(@pInputList,1,@vCurrentIndex - 0 - 1))),  
            @vCurrentIndex,
            @vNextIndex
        WHERE 
            @vCurrentIndex > 0
        UNION ALL
        SELECT 
            RTRIM(LTRIM(SUBSTRING(@pInputList,CurrentIndex + @vLengthOfDelimiter, CASE WHEN NextIndex = 0 THEN @vLengthOfInputList + 1 ELSE NextIndex - CurrentIndex -@vLengthOfDelimiter END))), 
            NextIndex,
            CAST(CHARINDEX(@pDelimiter,@pInputList,NextIndex + @vLengthOfDelimiter) AS INT)
        FROM InputList WHERE CurrentIndex <> 0
    )
    INSERT INTO @List
    SELECT Input FROM InputList
    
    RETURN
END

GO