-- This is the basic idea for the CTE method.
CREATE FUNCTION cte_split_inline (@list  nvarchar(MAX),
                                  @delim nchar(1) = ',') RETURNS TABLE AS
RETURN
   WITH csvtbl(start, stop) AS (
     SELECT start = convert(bigint, 1),
            stop = charindex(@delim COLLATE Slovenian_BIN2,
                             @list + convert(nvarchar(MAX), @delim))
     UNION ALL
     SELECT start = stop + 1,
            stop = charindex(@delim COLLATE Slovenian_BIN2,
                             @list + convert(nvarchar(MAX), @delim), stop + 1)
     FROM   csvtbl
     WHERE  stop > 0
  )
  SELECT ltrim(rtrim(convert(nvarchar(4000),
            substring(@list, start,
                      CASE WHEN stop > 0 THEN stop - start ELSE 0 END)
         ))) AS Value
  FROM   csvtbl
  WHERE  stop > 0
go

-- The same, but with DISTINCT
CREATE FUNCTION cte_split_distinct (@list  nvarchar(MAX),
                                  @delim nchar(1) = ',') RETURNS TABLE AS
RETURN
   WITH csvtbl(start, stop) AS (
     SELECT start = convert(bigint, 1),
            stop = charindex(@delim COLLATE Slovenian_BIN2,
                             @list + convert(nvarchar(MAX), @delim))
     UNION ALL
     SELECT start = stop + 1,
            stop = charindex(@delim COLLATE Slovenian_BIN2,
                             @list + convert(nvarchar(MAX), @delim), stop + 1)
     FROM   csvtbl
     WHERE  stop > 0
  )
  SELECT DISTINCT ltrim(rtrim(convert(nvarchar(4000),
            substring(@list, start,
                      CASE WHEN stop > 0 THEN stop - start ELSE 0 END)
         ))) AS Value
  FROM   csvtbl
  WHERE  stop > 0
go


-- The same as above, but this is a multi-statement function.
CREATE FUNCTION cte_split_mstmt (@list  nvarchar(MAX),
                                 @delim nchar(1) = ',')
RETURNS @t TABLE (str nvarchar(4000) NOT NULL) AS
BEGIN
   WITH csvtbl(start, stop) AS (
     SELECT start = convert(bigint, 1),
            stop = charindex(@delim COLLATE Slovenian_BIN2,
                             @list + convert(nvarchar(MAX), @delim))
     UNION ALL
     SELECT start = stop + 1,
            stop = charindex(@delim COLLATE Slovenian_BIN2,
                             @list + convert(nvarchar(MAX), @delim), stop + 1)
     FROM   csvtbl
     WHERE  stop > 0
  )
  INSERT @t(str)
     SELECT ltrim(rtrim(substring(@list, start,
                                  CASE WHEN stop > 0 THEN stop - start ELSE 0 END)))
     FROM   csvtbl
     WHERE  stop > 0
  OPTION (MAXRECURSION 0)

  RETURN
END
go

-- And this is a chunked version.
CREATE FUNCTION cte_split_chunk (@list  nvarchar(MAX),
                                 @delim nchar(1) = ',')
RETURNS @t TABLE (str nvarchar(4000) NOT NULL) AS
BEGIN
   DECLARE @slice nvarchar(4000),
           @textpos int,
           @maxlen  int,
           @stoppos int,
           @lastone bit

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

      ;WITH csvtbl(start, stop) AS (
         SELECT start = 1,
                stop  = charindex(@delim COLLATE Slovenian_BIN2, @slice)
         UNION ALL
         SELECT start = stop + 1,
                stop  = charindex(@delim COLLATE Slovenian_BIN2,
                                  @slice, stop + 1)
         FROM   csvtbl
         WHERE  stop > 0
      )
      INSERT @t (str)
         SELECT ltrim(rtrim(
                substring(@slice, start,
                          CASE WHEN stop > 0 THEN stop - start ELSE 0 END)))
         FROM   csvtbl
         WHERE  stop > 0
         OPTION (MAXRECURSION 0)
   END

   RETURN
END
go



-- This is a very cute method (not mentioned in the article) suggested yb
-- SQL Server MVP Itzik Ben-Gan, that uses a recursive CTE to do a binary
-- search. Cute as it may be, performance is poor.
CREATE FUNCTION cte_inline_binsearch(@s     nvarchar(MAX),
                                     @delim nchar(1) = N',')
RETURNS TABLE
AS
RETURN
  WITH BinSplit
  AS
  (
    SELECT @s AS s, len(@s) AS l,
      CASE WHEN charindex(@delim COLLATE Slovenian_BIN2, @s, len(@s) / 2) > 0
           THEN charindex(@delim COLLATE Slovenian_BIN2, @s, LEN(@s) / 2)
           ELSE charindex(@delim COLLATE Slovenian_BIN2, @s)
      END AS p

    UNION ALL

    SELECT s, len(s),
           CASE WHEN charindex(@delim COLLATE Slovenian_BIN2, s, LEN(s) / 2) > 0
                THEN CHARINDEX(@delim COLLATE Slovenian_BIN2, s, LEN(s) / 2)
                ELSE CHARINDEX(@delim COLLATE Slovenian_BIN2, s)
           END AS p
    FROM (SELECT CASE n WHEN 1 THEN left(s, p-1) ELSE right(s, l-p) END AS s
          FROM   BinSplit
          CROSS  JOIN  (SELECT 1 AS n
                        UNION ALL
                        SELECT 2) AS Nums
          WHERE p > 0) AS D
  )
  SELECT ltrim(rtrim(s)) AS str
  FROM BinSplit
  WHERE p = 0;