Split delimited fixed length with position table valued function SQL Server

Split function which returns result set as table from delimited list (comma, pipe, space, colon etc.):

CREATE FUNCTION [dbo].[SplitDelimiter] (

       @Str AS VARCHAR(MAX),

       @Delimiter AS VARCHAR(10)

)

RETURNS @FLT TABLE (Item VARCHAR(MAX))

AS

BEGIN

       DECLARE @Length AS INT

       SET @Length = CASE CHARINDEX(@Delimiter, @Str)

                     WHEN 0

                           THEN LEN(@Str) + 1

                     ELSE CHARINDEX(@Delimiter, @Str)

                     END

       WHILE LEN(@Str) <> 0

       BEGIN

              INSERT INTO @FLT

              SELECT LEFT(@Str, @Length - 1)

              SET @Str = STUFF(@Str, 1, @Length, '')

              SET @Length = CASE CHARINDEX(@Delimiter, @Str)

                           WHEN 0

                                  THEN LEN(@Str) + 1

                           ELSE CHARINDEX(@Delimiter, @Str)

                           END

       END

       RETURN

END

Example:

SELECT *

FROM dbo.SplitDelimiter('a,b,c', ',')

Output:

Item

a

b

c

Split function with given delimiter with rank or position:

CREATE FUNCTION [dbo].[SplitDelimiterLengthWithRank] (

       @Str AS VARCHAR(MAX),

       @Delimiter AS VARCHAR(10))

RETURNS @FLT TABLE (Item VARCHAR(MAX), Position INT)

AS

BEGIN

       DECLARE @Posting AS INT

       DECLARE @Length AS INT

       SET @Posting = 1

       SET @Length = CASE CHARINDEX(@Delimiter, @Str)

                     WHEN 0

                           THEN LEN(@Str) + 1

                     ELSE CHARINDEX(@Delimiter, @Str)

                     END

       WHILE LEN(@Str) <> 0

       BEGIN

              INSERT INTO @FLT

              SELECT LEFT(@Str, @Length - 1), @Posting

              SET @Str = STUFF(@Str, 1, @Length, '')

              SET @Length = CASE CHARINDEX(@Delimiter, @Str)

                           WHEN 0

                                  THEN LEN(@Str) + 1

                           ELSE CHARINDEX(@Delimiter, @Str)

                           END

              SET @Posting += 1

       END

       RETURN

END

Example:

SELECT *

FROM dbo.SplitDelimiterLengthWithRank('a,b,c', ',')

Output:

Item	Position
a	1
b	2
c	3

Split function with fixed length:

CREATE FUNCTION [dbo].[SplitFixedLength] (

       @Str AS VARCHAR(MAX),

       @Length AS INT)

RETURNS @FLT TABLE (Item VARCHAR(MAX))

AS

BEGIN

       WHILE LEN(@Str) <> 0

       BEGIN

              INSERT INTO @FLT

              SELECT LEFT(@Str, @Length)

              SET @Str = STUFF(@Str, 1, @Length, '')

       END

       RETURN

END

Example:

SELECT *

FROM dbo.SplitFixedLength('abcdde', 2)

Output:

Item

ab

cd

de

Split function with fixed length with rank or position:

CREATE FUNCTION [dbo].[SplitFixedLengthWithRank] (

       @Str AS VARCHAR(MAX),

       @Length AS INT)

RETURNS @FLT TABLE (Item VARCHAR(MAX), Position INT)

AS

BEGIN

       DECLARE @Posting AS INT

       SET @Posting = 1

       WHILE LEN(@Str) <> 0

       BEGIN

              INSERT INTO @FLT

              SELECT LEFT(@Str, @Length), @Posting

              SET @Str = STUFF(@Str, 1, @Length, '')

              SET @Posting += 1

       END

       RETURN

END

Example:

SELECT *

FROM dbo.SplitFixedLengthWithRank('abcdde', 2)

Output:

Item	Position
ab	1
cd	2
de	3