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.
CodePudding user response:
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
CodePudding user response:
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,',',')