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)
AS
BEGIN
    declare
        @left nvarchar(max),
        @right nvarchar(max) = @string,
        @length int = len(@string),
        @next_position int = 1

    while @nth_element > 0
    begin
        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
    end
    return @left
END;
GO

 

Advertisements

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: