Interesting - does this work out faster than using the REPLACE function?
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)
SELECT @derived_string=STUFF((SELECT '' + (Case When
PATINDEX('%[' + @delimiter + ']%',SUBSTRING(@String,Nums.n,1)) >0
Then ',' else LTRIM(RTRIM(SUBSTRING(@String,Nums.n,1))) end)
FROM N4 Nums WHERE Nums.n<=LEN(@String) FOR XML PATH('')),1,0,'')
The section of code after this in your function is a simple XML string splitter. This article [/url]discusses the relative merits and otherwise of several types of string splitter and exposes the two main issues with XML string splitting - poor performance and sensitivity to XML control characters.
Other issues are:
OVER(ORDER BY X.n) will introduce a sort into the plan, OVER(ORDER BY (SELECT NULL)) won't.
There's no ORDER BY in the FOR XML PATH concatenator - I don't think you can guarantee the order of the elements in the reassembled string.