Split String

split-string.sql

/*
Split a string by delimiter and return substrings

    
Args:
    @string      NVARCHAR(max),
    @delimeter   NVARCHAR(100) = ''
  
Returns:
    TABLE 
    (
        -- Substring ID
        id        INT NOT NULL identity (1,1),
        -- Substring position
        position  INT NOT NULL,
        -- Substring token
        word      NVARCHAR(max) NOT NULL
    )

Example:
    select *
    from dbo.fnc_splitString ('alpha beta gamma')

    -> Id  position   word
       ---------------------
        1         1   alpha
        2         7   beta
        3        12   gamma
*/

create function dbo.fnc_splitString
(
    @string      NVARCHAR(max),
    @delimeter   NVARCHAR(100) = ''
)
returns 
@words table 
(
    id        int not null identity (1,1),
    position  int not null,
    word      nvarchar(max) not null
)
as
begin
    declare @string_len     int = len(@string)
    declare @delimeter_len  int = len(@delimeter)
    declare @begin_pos      int = 1
    declare @end_pos        int = 1
    declare @current_len    int = 0

    if @delimeter = '' and not @delimeter = ' '
    begin
        set @current_len = 1

        while @begin_pos <= @string_len
        begin
            insert into @words (position, word)
            values (@begin_pos,
                    substring (@string, @begin_pos, @current_len))
            set @begin_pos = @begin_pos + 1
        end
    end
    else
    begin
        while @end_pos <= @string_len
        begin
            set @end_pos   = charindex(@delimeter, @string, @begin_pos + 1) - 1

            if @end_pos = -1
                set @end_pos = @string_len
            set @current_len = @end_pos - @begin_pos + 1

            insert into @words (position, word)
            values (@begin_pos,
                    substring (@string, @begin_pos, @current_len))

            set @begin_pos = charindex(@delimeter, @string, @end_pos + 1) + @delimeter_len
            if @end_pos >= @string_len
                break
        end
    end
    
	return 
end
go