-- The inline_split_me as presented in the article, originally from Anith Sen
-- and with a modification from Brian W Perrin, where he reduces the number of
-- concatenations with the delimiter to two. Here using fn_nums rather than a
-- table.
CREATE FUNCTION fn_inline_split_me(@param nvarchar(MAX))
RETURNS TABLE
AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
                  substring(@param, Number,
                            charindex(N',' COLLATE Slovenian_BIN2,
                                      @param + convert(nvarchar(MAX), N','), Number) -
                            Number)
              ))) AS Value
       FROM   fn_nums(len(@param))
       WHERE  substring(convert(nvarchar(MAX), N',') + @param, Number, 1) = N',' COLLATE Slovenian_BIN2)
go

-- The DISTINCT version
CREATE FUNCTION fn_inline_split_me_distinct(@param nvarchar(MAX))
RETURNS TABLE
AS
RETURN(SELECT DISTINCT ltrim(rtrim(convert(nvarchar(4000),
                  substring(@param, Number,
                            charindex(N',' COLLATE Slovenian_BIN2,
                                      @param + convert(nvarchar(MAX), N','), Number) -
                            Number)
              ))) AS Value
       FROM   fn_nums(len(@param))
       WHERE  substring(convert(nvarchar(MAX), N',') + @param, Number, 1) = N',' COLLATE Slovenian_BIN2)
go

-- This multi-statement function is the same as fn_inline_split_me.
CREATE FUNCTION fn_mstmt_split_me(@param nvarchar(MAX))
RETURNS @t TABLE (str nvarchar(4000) NOT NULL) AS
BEGIN
  INSERT @t(str)
      SELECT ltrim(rtrim(
                  substring(@param, Number,
                            charindex(N',' COLLATE Slovenian_BIN2,
                                      @param + convert(nvarchar(MAX), N','), Number) -
                            Number)
              )) AS Value
       FROM   fn_nums(len(@param))
       WHERE  substring(convert(nvarchar(MAX), N',') + @param, Number, 1) = N',' COLLATE Slovenian_BIN2
  RETURN
END
go

-- This multi-statement splices the list into chunks and has one output column
-- only. This corrersponds to the contender FNNUM$CHUNK. Note that we have
-- to put the output from fn_nums into table variable for best performance.
CREATE FUNCTION fn_chunk_split_me(@list  nvarchar(MAX),
                                  @delim nchar(1) = N',')
RETURNS @t TABLE (str nvarchar(4000) NOT NULL) AS
BEGIN
   DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
   DECLARE @numbers TABLE (Number int NOT NULL PRIMARY KEY)
   DECLARE @slice nvarchar(4000),
           @textpos int,
           @maxlen  int,
           @stoppos int

   SELECT @textpos = 1, @maxlen = 4000 - 2
   WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
   BEGIN
      SELECT @slice = substring(@list, @textpos, @maxlen)
      SELECT @stoppos = @maxlen -
                        charindex(@delim COLLATE Slovenian_BIN2, reverse(@slice))
      INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
      SELECT @textpos = @textpos - 1 + @stoppos + 2   -- On the other side of the comma.
   END
   INSERT @slices (slice)
       VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)

   INSERT @numbers(Number)
      SELECT Number FROM fn_nums(CASE WHEN len(@list) < @maxlen
                                      THEN len(@list)
                                      ELSE @maxlen
                                 END + 2)

   INSERT @t (str)
      SELECT ltrim(rtrim(substring(s.slice, N.Number + 1,
              charindex(@delim COLLATE Slovenian_BIN2, s.slice, N.Number + 1) -
              N.Number - 1))) AS Value
      FROM  @numbers N
      JOIN  @slices s ON N.Number <= len(s.slice) - 1
                     AND substring(s.slice, N.Number, 1) = @delim COLLATE Slovenian_BIN2

   RETURN
END
go

-- In this version, we fn_nums() directly with slices. This works well with
-- the fn_nums that uses TOP, but is a disaster with the fn_nums that only has
-- a WHERE clause.
CREATE FUNCTION fn_chunk_split_me2(@list  nvarchar(MAX),
                                  @delim nchar(1) = N',')
RETURNS @t TABLE (str nvarchar(4000) NOT NULL) AS
BEGIN
   DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
   DECLARE @slice nvarchar(4000),
           @textpos int,
           @maxlen  int,
           @stoppos int

   SELECT @textpos = 1, @maxlen = 4000 - 2
   WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
   BEGIN
      SELECT @slice = substring(@list, @textpos, @maxlen)
      SELECT @stoppos = @maxlen -
                        charindex(@delim COLLATE Slovenian_BIN2, reverse(@slice))
      INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
      SELECT @textpos = @textpos - 1 + @stoppos + 2   -- On the other side of the comma.
   END
   INSERT @slices (slice)
       VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)

   INSERT @t (str)
      SELECT ltrim(rtrim(substring(s.slice, N.Number + 1,
              charindex(@delim COLLATE Slovenian_BIN2, s.slice, N.Number + 1) -
              N.Number - 1))) AS Value
      FROM   @slices s
      CROSS  APPLY fn_nums(len(s.slice) - 1) AS N
      WHERE substring(s.slice, N.Number, 1) = @delim COLLATE Slovenian_BIN2
   RETURN
END
go