String Parsing Function

This handy little script parses a string and returns the results as a table. I know there are a ton of string parsing functions out there, but I thought I’d add to the list. ;)

The basic logic of it (using a CTE) is derived from a forum post I found years ago. The table-valued UDF, delimiter, etc. is all stuff that I added. So thus, while I can not claim complete credit, I still thought it’d be worthwhile to share. Plus this will be used in another script I will be posting soon. :)

/* Let's create our parsing function... */
CREATE FUNCTION dbo.dba_parseString_udf
          @stringToParse VARCHAR(8000)  
        , @delimiter     CHAR(1)
RETURNS @parsedString TABLE (stringValue VARCHAR(128))
    Name:       dba_parseString_udf
    Author:     Michelle Ufford,
    Purpose:    This function parses string input using a variable delimiter.
    Notes:      Two common delimiter values are space (' ') and comma (',')
    Date        Initials    Description
    2011-05-20  MFU         Initial Release
    SELECT *
	FROM dba_parseString_udf(<string>, <delimiter>);
Test Cases:
    1.  multiple strings separated by space
        SELECT * FROM dbo.dba_parseString_udf('  aaa  bbb  ccc ', ' ');
    2.  multiple strings separated by comma
        SELECT * FROM dbo.dba_parseString_udf(',aaa,bbb,,,ccc,', ',');
    /* 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)
        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);
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , , , . Bookmark the permalink.

4 Responses to String Parsing Function

  1. Pingback: Log Buffer #221, A Carnival of the Vanities for DBAs | The Pythian Blog

  2. Pingback: Log Buffer #221, A Carnival of the Vanities for DBAs | The Pythian Blog

  3. Michael says:

    This is exactly what I have been looking for!!! Thanks for posting it!

  4. Jeff Stevens says:

    Michelle, this is perfect for what I needed to do, and by far the best example I came across. Thank you for sharing it.


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>