Simple SQL Server Split Function

 

I know there are a lot of solutions to this on the internet, here’s mine!

Sample use-cases:

 

Code:

 

CREATE FUNCTION [dbo].[Split]
    (
      @list NTEXT ,
      @Delim CHAR(1) = ','
    )
RETURNS @tbl TABLE
    (
      items VARCHAR(4000) NOT NULL
    )
AS 
    BEGIN
        DECLARE @pos INT ,
            @textpos INT ,
            @chunklen SMALLINT ,
            @str NVARCHAR(4000) ,
            @tmpstr NVARCHAR(4000) ,
            @leftover NVARCHAR(4000)
        IF @Delim IS NULL 
            BEGIN
                INSERT  INTO @tbl
                        ( items )
                VALUES  ( CAST(@list AS VARCHAR)  -- items - varchar(4000)
                          )
                RETURN
            END
        SET @textpos = 1
        SET @leftover = ''
        WHILE @textpos <= DATALENGTH(@list) / 2 
            BEGIN
                SET @chunklen = 4000 - DATALENGTH(@leftover) / 2
                SET @tmpstr = LTRIM(@leftover + SUBSTRING(@list, @textpos,
                                                          @chunklen))
                SET @textpos = @textpos + @chunklen
                SET @pos = CHARINDEX(@Delim, @tmpstr)
                WHILE @pos > 0 
                    BEGIN
                        SET @str = SUBSTRING(@tmpstr, 1, @pos - 1)
                        IF LTRIM(RTRIM(@str)) <> '' 
                            INSERT  @tbl
                                    ( items )
                            VALUES  ( @str )
                        SET @tmpstr = LTRIM(SUBSTRING(@tmpstr, @pos + 1,
                                                      LEN(@tmpstr)))
                        SET @pos = CHARINDEX(@Delim, @tmpstr)
                    END
                SET @leftover = @tmpstr
            END
        IF LTRIM(RTRIM(@leftover)) <> '' 
            INSERT  @tbl
                    ( items )
            VALUES  ( @leftover )
        RETURN
    END