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