-- The simple fixed-length function, which will return incorrect results,
-- if the list length exceeds the highest number in Numbers.
CREATE FUNCTION fixstring_single(@str nvarchar(MAX), @itemlen tinyint)
RETURNS TABLE
RETURN(SELECT listpos = n.Number,
              str = rtrim(convert(nvarchar(4000),
                      substring(@str, @itemlen * (n.Number - 1) + 1, @itemlen)))
       FROM   Numbers n
       WHERE  n.Number <= len(@str) / @itemlen +
              CASE len(@str) % @itemlen WHEN 0 THEN 0 ELSE 1 END)
go

-- The same as fixstring_single, but as a multi-statement function.
CREATE FUNCTION fixstring_mstmt(@str nvarchar(MAX), @itemlen tinyint)
   RETURNS @t TABLE  (listpos int NOT NULL,
                     str     nvarchar(4000) NOT NULL) AS
BEGIN
   INSERT @t (listpos, str)
       SELECT listpos = n.Number,
              str = rtrim(
                      substring(@str, @itemlen * (n.Number - 1) + 1, @itemlen))
       FROM   Numbers n
       WHERE  n.Number <= len(@str) / @itemlen +
              CASE len(@str) % @itemlen WHEN 0 THEN 0 ELSE 1 END
   RETURN
END
go

-- A special version for integers that works on a binary string.
CREATE FUNCTION fixbinary_single(@str varbinary(MAX))
RETURNS TABLE
AS
RETURN(SELECT listpos = n.Number,
              n = convert(int, substring(@str, 4 * (n.Number - 1) + 1, 4))
       FROM   Numbers n
       WHERE  n.Number <= datalength(@str) / 4 )
go

-- A more complex fixed-length function that self-join Numbers to increase
-- the longest supported list length to the practical infinity.
CREATE FUNCTION fixstring_multi(@str nvarchar(MAX), @itemlen tinyint)
   RETURNS TABLE AS
RETURN(SELECT listpos = n1.Number + m.maxnum * (n2.Number - 1),
              str = rtrim(convert(nvarchar(4000),
                    substring(@str,
                    @itemlen * (n1.Number + m.maxnum * (n2.Number - 1) - 1) + 1,
                    @itemlen)))
       FROM   Numbers n1
       CROSS JOIN (SELECT maxnum = MAX(Number) FROM Numbers) AS m
       JOIN   Numbers n2 ON
              @itemlen * (n1.Number + m.maxnum * (n2.Number - 1) - 1) + 1 <=
                        len(@str)
       WHERE  n2.Number <= len(@str) / (m.maxnum * @itemlen) + 1
         AND  n1.Number <= CASE WHEN len(@str) / @itemlen <= m.maxnum
                                THEN len(@str) / @itemlen +
                                     CASE len(@str) % @itemlen
                                          WHEN 0 THEN 0
                                          ELSE 1
                                     END
                                ELSE m.maxnum
                           END
)
go

-- The same idea as in fixstring_multi, cross-join Numbers to get more
-- numbers, but written somewhat differently.
CREATE FUNCTION fixstring_multi2(@str nvarchar(MAX), @itemlen tinyint)
  RETURNS TABLE AS
RETURN(
     SELECT listpos = (s.sliceno - 1) * (s.maxnum / @itemlen) + n.Number,
            str = rtrim(convert(nvarchar(4000),
                  substring(s.slice, @itemlen * (n.Number - 1) + 1, @itemlen)))
     FROM   (SELECT m.maxnum,
                    sliceno = n.Number,
                    slice = substring(@str,
                                      (m.maxnum - m.maxnum % @itemlen) *
                                            (n.Number - 1) + 1,
                                      m.maxnum - m.maxnum % @itemlen)
             FROM   Numbers n
             CROSS  JOIN (SELECT maxnum = MAX(Number) FROM Numbers) AS m
             WHERE  n.Number <= len(@str) /
                    (m.maxnum - m.maxnum % @itemlen) +
                    CASE len(@str) % (m.maxnum - m.maxnum % @itemlen)
                         WHEN 0 THEN 0
                         ELSE 1
                    END) AS s
     JOIN   Numbers n ON n.Number <= len(s.slice) / @itemlen +
              CASE len(s.slice) % @itemlen WHEN 0 THEN 0 ELSE 1 END
   )
go

-- The iterative method, but with fixed strings.
CREATE FUNCTION fixstring_iter(@str nvarchar(MAX), @itemlen tinyint)
  RETURNS @tbl TABLE (listpos  int            NOT NULL,
                      Value    nvarchar(4000) NOT NULL) AS
BEGIN
   DECLARE @i      int,
           @pos    int,
           @strlen int

   SELECT @i = 1, @pos = 1, @strlen = len(@str)
   WHILE @pos <= @strlen
   BEGIN
      INSERT @tbl (listpos, Value)
         SELECT @i, rtrim(substring(@str, @pos, @itemlen))
      SELECT @i = @i + 1, @pos = @pos + @itemlen
   END

   RETURN
END