Substring with union working differently in a User Defined Function

Question

I am trying to split a sentence with unlimited characters into multiples of 7 using a SQL UDF. Which means I am trying to break a sentence into rows with 7 characters in each rows. This is the approach that I felt was the quickest.

CREATE OR ALTER FUNCTION dbo.UDF_SplitStringIntoRows
(
    @inputstring nvarchar(MAX)
)
RETURNS @OutputTbl TABLE
(
    txt nvarchar(MAX),
    seq INT IDENTITY 
)
AS
BEGIN
    IF(LEN(@inputstring) <= 40)
    BEGIN 
        INSERT INTO @OutputTbl (txt) 
            SELECT SUBSTRING(@inputstring, 1,7) UNION 
            SELECT SUBSTRING(@inputstring, 8,7) UNION 
            SELECT SUBSTRING(@inputstring, 15,7) UNION 
            SELECT SUBSTRING(@inputstring, 23,7) UNION 
            SELECT SUBSTRING(@inputstring, 30,7) UNION 
            SELECT SUBSTRING(@inputstring, 37,7) UNION 
            SELECT SUBSTRING(@inputstring, 44,7)
    END

    RETURN
END

My query:

SELECT *
FROM dbo.UDF_SplitStringIntoRows('This is a demo function which') AS USSIR
WHERE USSIR.txt <> ''

Output:

Which is messing up the sequence of the sentence. Am I missing something here? Please suggest.

Your function appears to break into 7-characters per row which is not what you are asking, Sample data and desired results will probably help. — Stu, Aug 18 '21 at 19:56
There are much better ways to split strings with no need for multiple selects and unions, look into using `cross apply` with a *tally table* — Stu, Aug 18 '21 at 19:59
You also have an off-by-one error in your substring calculations — Charlieface, Aug 18 '21 at 20:22
Is `LEN(@inputstring) <= 40` consistent with `SUBSTRING(@inputstring, 44,7)`? — SMor, Aug 18 '21 at 20:26
If you only expect a string of 40 (44?) characters, why use `nvarchar(max)`? And what happens if you pass a string longer than 40 characters (function will be happy taking megabytes)? — Stu, Aug 18 '21 at 20:28

score 0 · Answer 1 · answered Aug 18 '21 at 19:58

0

Just figured out the bug. Its actually the Union that is messing it up. I just replaced Union with Union All and it seems to be fetching the desired output.

Thanks again.

answered Aug 18 '21 at 19:58

Surya Garimella

317
4
13

score 0 · Answer 2 · answered Aug 18 '21 at 20:36

You are much better off with an inline Table Function

CREATE OR ALTER FUNCTION dbo.UDF_SplitStringIntoRows
(
    @inputstring nvarchar(56),
    @splitSize int
)
AS RETURN

SELECT
    SUBSTRING(@inputstring, n * @splitSize + 1, @splitSize) txt,
    ROW_NUMBER() OVER (ORDER BY n) seq
FROM (VALUES
  (0),(1),(2),(3),(4),(5),(6),(7)
) v(n)
WHERE n * @splitSize + 1 >= LEN(@inputstring);

GO

If the string can be of unlimited length, you can use Itzik Ben-Gan's tally function to generate rows

CREATE OR ALTER FUNCTION dbo.GetNums
  (@low AS BIGINT = 1, @high AS BIGINT)
RETURNS TABLE
AS
RETURN
 
  WITH
    L0 AS ( SELECT 1 AS c 
            FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
                        (1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
    L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),
    L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),
    L3 AS ( SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B ),
    Nums AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
              FROM L3 )
  SELECT TOP(@high - @low + 1)
     rownum AS rn,
     @high + 1 - rownum AS op,
     @low - 1 + rownum AS n
  FROM Nums
  ORDER BY rownum;

GO

CREATE OR ALTER FUNCTION dbo.UDF_SplitStringIntoRows
(
    @inputstring nvarchar(MAX),
    @splitSize int
)
AS RETURN

SELECT
    SUBSTRING(@inputstring, n * @splitSize + 1, @splitSize) txt,
    rownum AS seq
FROM dbo.GetNums(0, (LEN(@inputstring) - 1) / @splitSize) v;

Substring with union working differently in a User Defined Function

2 Answers2