STRING_SPLIT arrives in SQL Server 2016

A string splitting function has finally been included in SQL Server 2016!!! It might come as a surprise that previous versions did not have such a rudimentary string function, but there you go.

I had a go at coming up with my own parse / extract string function while developing a data warehouse in SQL Server 2014. udf_parse takes a string and a delimiter (to split on), returning the nth element after splitting:

create function [dbo].[udf_parse](@string nvarchar(max), @delim nvarchar(max), @nth_element int)
RETURNS nvarchar(max)
        @left nvarchar(max),
        @right nvarchar(max) = @string,
        @length int = len(@string),
        @next_position int = 1

    while @nth_element > 0
        set @next_position = charindex(@delim, @right)
        set @left = substring(@right, 1, @next_position - 1)
        set @right = substring(@right, @next_position + 1, @length)

        set @length = len(@right)
        set @nth_element = @nth_element - 1
    return @left



Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: