This function uses a recursive CTE (Common Table Expression) to parse a delimited string into a table.
Here is a simple example of how the function can be used:
DECLARE @Gmins nvarchar(max)
SET @Gmins = ’100,101,102,103′;
SET @Gmins = @Gmins + ‘,’;
SELECT data FROM dbo.fn_SplitString_To_Table(@Gmins,’,')
The first parameter is the string that you want to parse, the second is the delimiter that you want to use to
split the string (normally a comma or a semi-colon).
Recursive CTE’s are limited to 100 levels of recursion by default. As soon as a string is split into more than 101 parts,
you will get an error stating that this limit has been exceeded.
If you change the value “102″ to “101″ the statement will work without any problems.
To bypass the limit of 100 levels of recursion, you need to add a query hint to the code – OPTION(MAXRECURSION 0)
CREATE FUNCTION [dbo].[fn_SplitString_To_Table]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS table
AS
RETURN
WITH Strings(data, r)
AS
(
SELECT
SUBSTRING(@string,1, CHARINDEX(@delimiter, @string)-1) data,
SUBSTRING(@string,CHARINDEX(@delimiter, @string)+1, len(@string)) r
UNION ALL
SELECT
SUBSTRING(r,1, CHARINDEX(@delimiter, r)-1) data,
SUBSTRING(r,CHARINDEX(@delimiter, r)+1, len(r)) r
FROM Strings
WHERE
CHARINDEX(@delimiter, r) > 0
)
SELECT data FROM Strings
