iTVF for splitting string into row of substrings based on a specified separator character breaks when received empty value (TSQL)

Question

I have an inline table-valued function, which splits strings into row of substrings based on a specified separator.

It is as follows:

ALTER FUNCTION [dbo].[SplitString]
    (@List NVARCHAR(MAX),
     @Delim VARCHAR(255))
RETURNS TABLE
AS
    RETURN 
        (SELECT [Value], idx = RANK() OVER (ORDER BY n) 
         FROM 
             (SELECT 
                  n = Number, 
                  [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
                  CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
              FROM 
                  (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
                   FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
                AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim) AS y
        );
GO

Usage:

SELECT value 
FROM dbo.SplitString('a|b|c', '|')

returns:

value
a
b
c

But when sending an empty value as the first argument, it doesn't return anything.

For example:

SELECT value FROM dbo.SplitString('','|')

This doesn't return anything.

What modification I need to do to the dbo.SplitString function, so that it returns an empty result set, when an empty string is passed in as first argument?

PS: I can't use the inbuilt STRING_SPLIT function because of compatibility issues.

[`DelimitedSplit8k_LEAD`](https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function), [XML Splitter](https://www.mssqltips.com/sqlservertip/1771/splitting-delimited-strings-using-xml-in-sql-server/) — Thom A, Nov 17 '22 at 11:30
@Larnu I cant write new functions, I need to modify the same — Karthik Karnam, Nov 17 '22 at 11:32
So modify your function with the definition of one of the above solutions, then you aren't creating a *new* function. — Thom A, Nov 17 '22 at 11:33
If you're on SQL Server 2016 why aren't you using the built-in (and much more efficient) [string_split()](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql) function? — AlwaysLearning, Nov 17 '22 at 12:16
@AlwaysLearning, because of compatibility version, I cant use that. I need to have a user defined function which achieves the same. — Karthik Karnam, Nov 17 '22 at 12:29
@Larnu, Even with methods you suggested fails to perform when sent empty strings :( — Karthik Karnam, Nov 17 '22 at 13:05
I literally can't replicate that, @KarthikKarnam [db<>fiddle](https://dbfiddle.uk/w6hDxzGE); both return 1 row. — Thom A, Nov 17 '22 at 13:10

score 1 · Answer 1 · answered Nov 17 '22 at 23:34

DelimitedSplit8K_LEAD (above) will always return a row and will be faster.

That said, for learning purposes let's fix your function. If you replace a blank value with your delimiter you will get the results you are looking for. You just need to replace every instance of @list with ISNULL(NULLIF(@List,''),@Delim). Now you have:

ALTER FUNCTION [dbo].[SplitString]
    (@List NVARCHAR(MAX),
     @Delim VARCHAR(255))
RETURNS TABLE
AS
    RETURN 
        (SELECT [Value], idx = RANK() OVER (ORDER BY n) 
         FROM 
             (SELECT 
                  n = Number, 
                  [Value] =     LTRIM(RTRIM(SUBSTRING(ISNULL(NULLIF(@List,''),@Delim), [Number],
              CHARINDEX(@Delim, ISNULL(NULLIF(@List,''),@Delim) + @Delim, [Number]) - [Number])))
              FROM 
                  (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
                   FROM sys.all_objects) AS x
              WHERE Number <= LEN(ISNULL(NULLIF(@List,''),@Delim))
                AND SUBSTRING(@Delim + ISNULL(NULLIF(@List,''),@Delim),         
[Number], LEN(@Delim)) = @Delim) AS y
        );

Now, when you execute: DECLARE @list VARCHAR(max) = '', @delim VARCHAR(255) = '|'

SELECT *
FROM dbo.SplitString(@list,@delim)

You get:

Value    idx
------   ------
         1

Thanks for your efforts @Bernie. I really appreciate it. DelimitedSplit8K_LEAD is working :) But I cant change this function very dynamically(as it exist in our code base from many days) And also there is one problem in your answer. When you give empty string at the end, It fails to return. Eg: select * from [dbo].[SplitString]('a,b,',',') returns only "a,b" but it must return "a,b, " for me . — Karthik Karnam, Nov 18 '22 at 06:20

score 0 · Accepted Answer · answered Nov 18 '22 at 06:36

Thanks @Larnu and @Bernie for all suggestions.
After so much of research, I started iterating and getting expected result. I achieved this by simple while loop and , string functions of SQL.

CREATE FUNCTION [SplitString]
(
   @ActualString      VARCHAR(MAX),
   @DelimiterCharacter  VARCHAR(10)
)
RETURNS @TableRes TABLE (Id INT IDENTITY(1,1),Value VARCHAR(MAX))
AS
BEGIN
DECLARE @SubStr VARCHAR(MAX)
     WHILE (CHARINDEX(@DelimiterCharacter  ,@ActualString)<>0)
     BEGIN
         SET @SubStr=SUBSTRING(@ActualString,1,CHARINDEX(@DelimiterCharacter ,@ActualString)-1)
         SET @ActualString= STUFF(@ActualString,1,CHARINDEX(@DelimiterCharacter,@ActualString),'') 
         INSERT INTO @TableRes
         SELECT @SubStr

      END
       INSERT INTO @TableRes
       SELECT @ActualString


       RETURN
END

This will work for all cases
1)When Actual string is empty string like select * from [dbo].[SplitString]('',',')
2)When Actual string has empty string at end like select * from [dbo].[SplitString]('a,b,',',')

iTVF for splitting string into row of substrings based on a specified separator character breaks when received empty value (TSQL)

2 Answers2