Split Delimited String

Whether you like it or not, if you develop applications, at some point data will be collected as a delimited string and you’ll need to parse it within the database. While a database developer might prefer to work with sets or arrays, delimited strings are a fact of life; deal with it.

There are few good ways of taking a delimited string and breaking it up into discrete values, but, depending on the version of SQL you are using, some are better than others. If you’re running SQL 2005 or later, then you are in luck: your version supports the XML object type and you can make use of one of the most elegant ways of splitting a delimited string.

This method has been around for a while, but, nevertheless, it is one of my favorite utility functions and invariably finds its way into the model or master database on any server I have occasion to develop on.

-- Create a user-defined, table-valued function
CREATE FUNCTION utfSplitString (
    @nstrValues NVARCHAR(MAX) -- the delimited string of values
    , @strDelimiter CHAR(1) -- the delimiting character
    )
RETURNS @tblReturn TABLE (
    Value NVARCHAR(MAX)
    )
AS
BEGIN

    -- Convert the values to XML, replacing the delimiter with a node break
    DECLARE @x XML = CONVERT(
        XML
        , '<root><n>'
            + REPLACE(@nstrValues, @strDelimiter, '</n><n>')
            + '</n></root>'
        )
    ;

    -- Parse the XML into the return table using native XML methods
    INSERT INTO @tblReturn (Value)
    SELECT x.v.value('.', 'VARCHAR(MAX)')
    FROM @x.nodes('/root/n') x(v)
    ;

    -- Return the table records
    RETURN;

END

With the function created, you can now use it to break a delimited string into values. Simply pass in the string and the delimiter and off you go. As follows:

-- Use the function to break a string into values
SELECT Value
FROM utfSplitString(N'Hello,World,!', ',')
;

Does anyone have a more elegant way to split a delimited string? If so, please share it as a comment.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: