split text into rows: The maximum recursion 100 has been exhausted before statement completion

Question

split text into rows: The maximum recursion 100 has been exhausted before statement completion

marc.corbeel

Default port

Points: 1484
More actions
January 16, 2019 at 2:14 am

#379024

I have a function that splits text with a comma as separator into rows.
I used to do this with a xml function but had too many issues with illegal characters. I searched and found a good alternative with a recursive function:
create function [dbo].[tvf_SplitCommaSeparatedString](@in nvarchar(max)) returns table as Return
with tmp (word, ix) as (select @in , CHARINDEX('', @in) --Recu. start, ignored val to get the types right union all select Substring(@in, ix+1, ix2-ix-1), ix2 from (Select *, CHARINDEX(',', @in + ',', ix+1) ix2 from tmp) x where ix2<>0)
select word from tmp where ix<>0
Problem now is that with a long string to be parsed, I get error: The maximum recursion 100 has been exhausted before statement completion.
How can I solve this?
Here is a test
declare @r nvarchar(max) = ''
declare @i int = 0 declare @max-2 int = 105 while @i < @max-2 begin set @i += 1 set @r += ',' + format(@i, '0') end
set @r = substring(@r, 2, len(@r))
select * from dbo.tvf_SplitCommaSeparatedString(@r)
If you set @max-2 to 100 it works, higher than 100 it doesn't....

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23009 More actions · Answer 1

marc.corbeel - Wednesday, January 16, 2019 2:13 AM
I have a function that splits text with a comma as separator into rows.
I used to do this with a xml function but had too many issues with illegal characters. I searched and found a good alternative with a recursive function:
create function [dbo].[tvf_SplitCommaSeparatedString](@in nvarchar(max)) returns table as Return
with tmp (word, ix) as (select @in , CHARINDEX('', @in) --Recu. start, ignored val to get the types right union all select Substring(@in, ix+1, ix2-ix-1), ix2 from (Select *, CHARINDEX(',', @in + ',', ix+1) ix2 from tmp) x where ix2<>0)
select word from tmp where ix<>0
Problem now is that with a long string to be parsed, I get error: The maximum recursion 100 has been exhausted before statement completion.
How can I solve this?
Here is a test
declare @r nvarchar(max) = ''
declare @i int = 0 declare @max-2 int = 105 while @i < @max-2 begin set @i += 1 set @r += ',' + format(@i, '0') end
set @r = substring(@r, 2, len(@r))
select * from dbo.tvf_SplitCommaSeparatedString(@r)
If you set @max-2 to 100 it works, higher than 100 it doesn't....

The 'house' splitter here[/url] will do what you want very quickly and with no recursion limits.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

marc.corbeel Default port Points: 1484 More actions · Answer 2

marc.corbeel

Default port

Points: 1484

January 16, 2019 at 2:38 am

#2019042

Cool, thank you!

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 3

marc.corbeel - Wednesday, January 16, 2019 2:13 AM
create function [dbo].[tvf_SplitCommaSeparatedString](@in nvarchar(max)) returns table as Return with tmp (word, ix) as (select @in , CHARINDEX('', @in) --Recu. start, ignored val to get the types right union all select Substring(@in, ix+1, ix2-ix-1), ix2 from (Select *, CHARINDEX(',', @in + ',', ix+1) ix2 from tmp) x where ix2<>0)
select word from tmp where ix<>0
Problem now is that with a long string to be parsed, I get error: The maximum recursion 100 has been exhausted before statement completion.
How can I solve this?
Here is a test
declare @r nvarchar(max) = ''
declare @i int = 0 declare @max-2 int = 105 while @i < @max-2 begin set @i += 1 set @r += ',' + format(@i, '0') end
set @r = substring(@r, 2, len(@r))
select * from dbo.tvf_SplitCommaSeparatedString(@r)
If you set @max-2 to 100 it works, higher than 100 it doesn't....

You could add a hint to increase the maximum recursion level, the maximum level is 32,767.
You can't add this hint to the tvf function but you can add it to the select query that uses the tvf.

declare @r nvarchar(max) = '' declare @i int = 0 declare @max-2 int = 105 while @i < @max-2 begin set @i += 1 set @r += ',' + format(@i, '0') end

set @r = substring(@r, 2, len(@r))

select * from dbo.tvf_SplitCommaSeparatedString(@r) option (MAXRECURSION 1000)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

Since you are posting on the SQL Server 2017 forum, then you should use this function.
😎

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 5

SQL Server 2017 has its own STRING_SPLIT function, so no need to write your own.

declare @r nvarchar(max) = '' declare @i int = 0 declare @max-2 int = 105 while @i < @max-2 begin set @i += 1 set @r += ',' + format(@i, '0') end set @r = substring(@r, 2, len(@r))

select * from STRING_SPLIT((@r, ',')

marc.corbeel Default port Points: 1484 More actions · Answer 6

yes I know, but some clients still use version 2012... so I cannot use it

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 7

marc.corbeel - Wednesday, January 16, 2019 6:14 AM
yes I know, but some clients still use version 2012... so I cannot use it

If you want the quickest fix just add OPTION (MAXRECURSION 1000) to your select query.
If performance is an issue then Eirikur's code will be much faster and works with SQL 2012.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Jonathan AC Roberts - Wednesday, January 16, 2019 6:40 AM
marc.corbeel - Wednesday, January 16, 2019 6:14 AM
yes I know, but some clients still use version 2012... so I cannot use it
If you want the quickest fix just add OPTION (MAXRECURSION 1000) to your select query.
If performance is an issue then Eirikur's code will be much faster and works with SQL 2012.

https://www.sqlservercentral.com/Forums/FindPost1013407.aspx

MAXRECURSION(0) - no limit.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Jonathan AC Roberts - Wednesday, January 16, 2019 6:40 AM
marc.corbeel - Wednesday, January 16, 2019 6:14 AM
yes I know, but some clients still use version 2012... so I cannot use it
If you want the quickest fix just add OPTION (MAXRECURSION 1000) to your select query.
If performance is an issue then Eirikur's code will be much faster and works with SQL 2012.

I guess I'd never recommend trying to fix the recursive method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Jeff Moden - Wednesday, January 16, 2019 8:10 AM
Jonathan AC Roberts - Wednesday, January 16, 2019 6:40 AM
marc.corbeel - Wednesday, January 16, 2019 6:14 AM
yes I know, but some clients still use version 2012... so I cannot use it
If you want the quickest fix just add OPTION (MAXRECURSION 1000) to your select query.
If performance is an issue then Eirikur's code will be much faster and works with SQL 2012.
I guess I'd never recommend trying to fix the recursive method.

It's a little like choosing to use this for a few hazel nuts.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 11

ChrisM@Work - Wednesday, January 16, 2019 8:16 AM
Jeff Moden - Wednesday, January 16, 2019 8:10 AM
Jonathan AC Roberts - Wednesday, January 16, 2019 6:40 AM
marc.corbeel - Wednesday, January 16, 2019 6:14 AM
yes I know, but some clients still use version 2012... so I cannot use it
If you want the quickest fix just add OPTION (MAXRECURSION 1000) to your select query.
If performance is an issue then Eirikur's code will be much faster and works with SQL 2012.
I guess I'd never recommend trying to fix the recursive method.
It's a little like choosing to use this for a few hazel nuts.

Mate, you owe me a keyboard 😀
😎

Luckily, my laptop is "beerproof"

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 12

Eirikur Eiriksson - Wednesday, January 16, 2019 8:30 AM
ChrisM@Work - Wednesday, January 16, 2019 8:16 AM
Jeff Moden - Wednesday, January 16, 2019 8:10 AM
Jonathan AC Roberts - Wednesday, January 16, 2019 6:40 AM
marc.corbeel - Wednesday, January 16, 2019 6:14 AM
yes I know, but some clients still use version 2012... so I cannot use it
If you want the quickest fix just add OPTION (MAXRECURSION 1000) to your select query.
If performance is an issue then Eirikur's code will be much faster and works with SQL 2012.
I guess I'd never recommend trying to fix the recursive method.
It's a little like choosing to use this for a few hazel nuts.
Mate, you owe me a keyboard 😀
😎
Luckily, my laptop is "beerproof"

That seemed it was meant to be a sledgehammer to crack a nut joke. But I don't see how it relates to this? What's the sledgehammer? Surely not a weakly performing rCTE?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Jonathan AC Roberts - Wednesday, January 16, 2019 8:37 AM
Eirikur Eiriksson - Wednesday, January 16, 2019 8:30 AM
ChrisM@Work - Wednesday, January 16, 2019 8:16 AM
Jeff Moden - Wednesday, January 16, 2019 8:10 AM
Jonathan AC Roberts - Wednesday, January 16, 2019 6:40 AM
marc.corbeel - Wednesday, January 16, 2019 6:14 AM
yes I know, but some clients still use version 2012... so I cannot use it
If you want the quickest fix just add OPTION (MAXRECURSION 1000) to your select query.
If performance is an issue then Eirikur's code will be much faster and works with SQL 2012.
I guess I'd never recommend trying to fix the recursive method.
It's a little like choosing to use this for a few hazel nuts.
Mate, you owe me a keyboard 😀
😎
Luckily, my laptop is "beerproof"
That seemed it was meant to be a sledgehammer to crack a nut joke. But I don't see how it relates to this? What's the sledgehammer? Surely not a weakly performing rCTE?

rCTE's are really expensive compared to some very well known alternatives for this particular job.
Very powerful too, as the late, great Dwain Camps showed so well. This is why the analogy works.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 14

Jonathan AC Roberts - Wednesday, January 16, 2019 8:37 AM
Eirikur Eiriksson - Wednesday, January 16, 2019 8:30 AM
ChrisM@Work - Wednesday, January 16, 2019 8:16 AM
Jeff Moden - Wednesday, January 16, 2019 8:10 AM
Jonathan AC Roberts - Wednesday, January 16, 2019 6:40 AM
marc.corbeel - Wednesday, January 16, 2019 6:14 AM
yes I know, but some clients still use version 2012... so I cannot use it
If you want the quickest fix just add OPTION (MAXRECURSION 1000) to your select query.
If performance is an issue then Eirikur's code will be much faster and works with SQL 2012.
I guess I'd never recommend trying to fix the recursive method.
It's a little like choosing to use this for a few hazel nuts.
Mate, you owe me a keyboard 😀
😎
Luckily, my laptop is "beerproof"
That seemed it was meant to be a sledgehammer to crack a nut joke. But I don't see how it relates to this? What's the sledgehammer? Surely not a weakly performing rCTE?

The sledgehammer is the rCTE, and you are being very gentile using the word "weakly" here, which translates to "poorly" even if done "weekly"😉
😎
It still drive me bonkers, how many are using sub-optimal code for such a common thing as splitting DSV/CSVs.
Chris's post just mate me laugh when having a mouthful of a beautiful ale😀