Search code examples
sql-servert-sqlsplituser-defined-functionssql-server-2016

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


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.


Solution

  • 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,',',')