-- The inline_split_me as presented in the article, originally from Anith Sen
-- and with a modification from Brian W Perrin, where he reduces the number of
-- concatenations with the delimiter to two.
CREATE FUNCTION inline_split_me(@param nvarchar(MAX))
RETURNS TABLE
AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
charindex(N',' COLLATE Slovenian_BIN2,
@param + N',', Number) -
Number)
))) AS Value
FROM Numbers
WHERE Number <= convert(int, len(@param))
AND substring(N',' + @param, Number, 1) = N',' COLLATE Slovenian_BIN2)
go
-- This is the original inline_split_me function, before I got a suggestion
-- for modification from Brian W. Perrin.
CREATE FUNCTION inline_split_me_old(@param nvarchar(MAX))
RETURNS TABLE
AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
substring(N',' + @param + N',', Number + 1,
charindex(N',' COLLATE Slovenian_BIN2,
N',' + @param + N',', Number + 1) -
Number - 1)
))) AS Value
FROM Numbers
WHERE Number <= convert(int, len(N',' + @param + N',')) - 1
AND substring(N',' + @param + N',', Number, 1) = N',' COLLATE Slovenian_BIN2)
go
-- inline_split_me without forcing a binary collation.
CREATE FUNCTION inline_split_me_nobin(@param nvarchar(MAX))
RETURNS TABLE
AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
charindex(N',', @param + N',', Number) - Number)
))) AS Value
FROM Numbers
WHERE Number <= convert(int, len(@param))
AND substring(N',' + @param, Number, 1) = N',')
go
-- Inline_split_me without converting the length to integer.
CREATE FUNCTION inline_split_me_nocnv(@param nvarchar(MAX))
RETURNS TABLE
AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
charindex(N',' COLLATE Slovenian_BIN2,
@param + N',', Number) -
Number)
))) AS Value
FROM Numbers
WHERE Number <= len(@param)
AND substring(N',' + @param, Number, 1) = N',' COLLATE Slovenian_BIN2)
go
-- Here is another suggestion from Brian, where he is down to one delimiter.
-- Performance is more or less identical to the former.
CREATE FUNCTION inline_split_me_bw2(@param nvarchar(MAX))
RETURNS TABLE
AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
charindex(N',' COLLATE Slovenian_BIN2,
@param + N',', Number) -
Number)
))) AS Value
FROM Numbers
WHERE Number <= convert(int, len(@param))
AND (Number = 1 OR
substring(@param, Number - 1, 1) = N',' COLLATE Slovenian_BIN2))
go
-- Inspired by Brian, I wrote this that has no concatenations at all. It
-- performs better than the original inline_split_me for the test with a list
-- 10000 strings, but else performance is poorer.
CREATE FUNCTION inline_split_me_bw3(@param nvarchar(MAX))
RETURNS TABLE
AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
coalesce(
nullif(charindex(N',' COLLATE Slovenian_BIN2,
@param, Number), 0) - Number,
len(@param)
)
)
))) AS Value
FROM Numbers
WHERE Number <= convert(int, len(@param))
AND (Number = 1 OR
substring(@param, Number - 1, 1) = N',' COLLATE Slovenian_BIN2))
go
-- This multi-statement function is the same as inline_split_me.
CREATE FUNCTION mstmt_split_me(@param nvarchar(MAX))
RETURNS @t TABLE (str nvarchar(4000) NOT NULL) AS
BEGIN
INSERT @t(str)
SELECT ltrim(rtrim(
substring(@param, Number,
charindex(N',' COLLATE Slovenian_BIN2,
@param + N',', Number) -
Number)
)) AS Value
FROM Numbers
WHERE Number <= len(@param)
AND substring(N',' + @param, Number, 1) = N',' COLLATE Slovenian_BIN2
RETURN
END
go
-- This multi-statement splices the list into chunks and has one output column
-- only. This corrersponds to the contender TBLNUM.
CREATE FUNCTION chunk_split_me(@list nvarchar(MAX),
@delim nchar(1) = N',')
RETURNS @t TABLE (str nvarchar(4000) NOT NULL) AS
BEGIN
DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
DECLARE @slice nvarchar(4000),
@textpos int,
@maxlen int,
@stoppos int
SELECT @textpos = 1, @maxlen = 4000 - 2
WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
BEGIN
SELECT @slice = substring(@list, @textpos, @maxlen)
SELECT @stoppos = @maxlen -
charindex(@delim COLLATE Slovenian_BIN2, reverse(@slice))
INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
SELECT @textpos = @textpos - 1 + @stoppos + 2 -- On the other side of the comma.
END
INSERT @slices (slice)
VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)
INSERT @t (str)
SELECT ltrim(rtrim(substring(s.slice, N.Number + 1,
charindex(@delim COLLATE Slovenian_BIN2, s.slice, N.Number + 1) -
N.Number - 1))) AS Value
FROM Numbers N
JOIN @slices s ON N.Number <= len(s.slice) - 1
AND substring(s.slice, N.Number, 1) = @delim COLLATE Slovenian_BIN2
RETURN
END
go
-- The same function that is in the article with two output columns.
CREATE FUNCTION duo_chunk_split_me(@list nvarchar(MAX),
@delim nchar(1) = N',')
RETURNS @t TABLE (str nvarchar(4000) NOT NULL,
nstr nvarchar(4000) NOT NULL) AS
BEGIN
DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
DECLARE @slice nvarchar(4000),
@textpos int,
@maxlen int,
@stoppos int
SELECT @textpos = 1, @maxlen = 4000 - 2
WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
BEGIN
SELECT @slice = substring(@list, @textpos, @maxlen)
SELECT @stoppos = @maxlen -
charindex(@delim COLLATE Slovenian_BIN2, reverse(@slice))
INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
SELECT @textpos = @textpos - 1 + @stoppos + 2 -- On the other side of the comma.
END
INSERT @slices (slice)
VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)
;WITH stringget (str) AS (
SELECT ltrim(rtrim(substring(s.slice, N.Number + 1,
charindex(@delim COLLATE Slovenian_BIN2, s.slice, N.Number + 1) -
N.Number - 1)))
FROM Numbers N
JOIN @slices s
ON N.Number <= len(s.slice) - 1
AND substring(s.slice, N.Number, 1) = @delim COLLATE Slovenian_BIN2
)
INSERT @t (str, nstr)
SELECT str, str
FROM stringget
RETURN
END
go
-- This is attempt to break up in chunks, and still have an inline function.
-- I wrote it myself, and it was an interesting challenge to write. However,
-- performance is simply absymal.
CREATE FUNCTION chunk_inline_split_me(@list nvarchar(MAX),
@delim nchar(1) = N',')
RETURNS TABLE AS
RETURN (
WITH
FirstAndLast (sliecno, slice, noofslices, firstword, lastword, sliceno, secondN, penultimateN) AS
(SELECT sliceno, slice, noofslices,
CASE WHEN sliceno > 1
THEN substring(slice, 2, secondN - 2)
END,
CASE WHEN sliceno < noofslices THEN
substring(slice, penultimateN + 1, 4000 - 2 - penultimateN)
END, sliceno, secondN, penultimateN
FROM (SELECT sliceno, slice, noofslices = len(@list) / 3998 + 1,
charindex(@delim COLLATE Slovenian_BIN2, slice, 2) AS secondN,
4001 - charindex(@delim COLLATE Slovenian_BIN2, reverse(slice), 2)
AS penultimateN
FROM (SELECT sliceno = Number,
slice = convert(nvarchar(4000),
@delim + substring(@list, (Number - 1) * 3998 + 1, 3998) + @delim)
FROM Numbers
WHERE Number <= len(@list) / 3998 + 1) Slices) AS FaL
)
SELECT row_number() OVER (ORDER BY f1.sliceno, N.Number) AS listpos,
ltrim(rtrim(substring(f1.slice, N.Number + 1,
charindex(@delim COLLATE Slovenian_BIN2, f1.slice, N.Number + 1) -
N.Number - 1) +
CASE WHEN N.Number = f1.penultimateN AND
f1.sliceno < f1.noofslices
THEN f2.firstword
ELSE ''
END)) AS Value
FROM Numbers N
JOIN FirstAndLast f1 ON N.Number <= len(f1.slice) - 1
AND substring(f1.slice, N.Number, 1) = @delim COLLATE Slovenian_BIN2
LEFT JOIN FirstAndLast f2 ON f1.sliceno + 1 = f2.sliceno
WHERE NOT (f1.sliceno > 1 AND N.Number = 1)
)