Monday, September 15, 2014

Splitting strings in SQL

Came across a great resource on splitting strings in SQL today by Aaron Bertrand here. I'm using the split by numbers approach. I noticed that when the last character(s) of the string being split is a delimiter, i.e. the last element would be NULL, the function chops off that last element. If only the function would accept the following IF logic somewhere:
if substring(@List,len(@list)-(len(@Delimiter)-1),len(@Delimiter)) = @Delimiter
SET @List = @List + @Delimiter
UPDATE
OK, I think this will work. See change highlighted in yellow. Testers wanted!
CREATE FUNCTION dbo.SplitStrings_Numbers
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = SUBSTRING(@List, Number,
CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
FROM dbo.Numbers
WHERE Number <= CONVERT(INT, LEN(@List)) +1
AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
);
GO

No comments:

Post a Comment

Thank you for your time and interest in this post!
Comments to this blog are sometimes moderated to prevent spam. Please don't be alarmed if your comment does not appear immediately.