TSQL Parse String

SELECT * FROM dbo.xx_dba_parseString_udf('  aaa  bbb  ccc ', ' ');

SELECT * FROM dbo.xx_dba_parseString_udf(',aaa,bbb,,,ccc,', ',');

GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
/* *** ************************************************************************************ *** */
GO
DECLARE @nvcObjectName sysname = N'dbo.xx_dba_parseString_udf';

IF OBJECT_ID(@nvcObjectName) IS NOT NULL
BEGIN;
	DECLARE @Stmt  NVARCHAR(256) = N'DROP FUNCTION ' + @nvcObjectName + N';' ;
	EXECUTE (@Stmt);

	IF OBJECT_ID(@nvcObjectName) IS NOT NULL
		RAISERROR('<<< FAILED DROPPING FUNCTION %s >>>', 16, 1,  @nvcObjectName);
	ELSE
		RAISERROR('<<< DROPPED FUNCTION %s >>>', 0, 1,  @nvcObjectName);
END;
GO
/* *** ************************************************************************************ *** */
GO

CREATE FUNCTION dbo.xx_dba_parseString_udf
(
	@stringToParse VARCHAR(8000),
	@delimiter     CHAR(1)
)
RETURNS @parsedString TABLE 
(
	stringValue    VARCHAR(128)
)
AS
/*********************************************************************************
    Name:       xx_dba_parseString_udf
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    This function parses string input using a variable delimiter.
 
    Notes:      Two common delimiter values are space (' ') and comma (',')
 
 Modification History

 Request    Date        Name                Description
 ---------  ----------  ------------------  -------------------------------------------- 
            2011-05-20  MFU                 Initial Release 
 VDOT       2017-04-07  John Wissenberg     Updated formatting

*********************************************************************************
Usage: 		
    SELECT *
    FROM xx_dba_parseString_udf(<string>, <delimiter>);
 
Test Cases:
 
    1.  multiple strings separated by space
        SELECT * FROM dbo.xx_dba_parseString_udf('  aaa  bbb  ccc ', ' ');
 
    2.  multiple strings separated by comma
        SELECT * FROM dbo.xx_dba_parseString_udf(',aaa,bbb,,,ccc,', ',');
*********************************************************************************/
BEGIN;
 
    /* Declare variables */
    DECLARE @trimmedString  VARCHAR(8000);
 
    /* We need to trim our string input in case the user entered extra spaces */
    SET @trimmedString = LTRIM(RTRIM(@stringToParse));
 
    /* Let's create a recursive CTE to break down our string for us */
    WITH parseCTE (StartPos, EndPos)
    AS
    (
        SELECT 
			1                                                                AS StartPos,
            CHARINDEX(@delimiter, @trimmedString + @delimiter)               AS EndPos

        UNION ALL

        SELECT 
			EndPos + 1                                                       AS StartPos,
            CHARINDEX(@delimiter, @trimmedString + @delimiter , EndPos + 1)  AS EndPos
        FROM parseCTE
        WHERE CHARINDEX(@delimiter, @trimmedString + @delimiter, EndPos + 1) <> 0
    )
 
    /* Let's take the results and stick it in a table */  
    INSERT INTO @parsedString
    SELECT SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)
    FROM parseCTE
    WHERE LEN(LTRIM(RTRIM(SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)))) > 0
    OPTION (MaxRecursion 8000);
 
    RETURN;   
END;
GO
/* *** ************************************************************************************ *** */
GO
DECLARE @nvcObjectName SYSNAME = N'dbo.xx_dba_parseString_udf';

IF OBJECT_ID(@nvcObjectName) IS NOT NULL
	RAISERROR('<<< CREATED FUNCTION %s >>>', 0, 1,  @nvcObjectName);
ELSE
	RAISERROR('<<< FAILED CREATING FUNCTION %s >>>', 16, 1,  @nvcObjectName);
GO
/* *** ************************************************************************************ *** */
GO