Nov 6, 2015

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

No comments:

Post a Comment