Convert String to a Table using CTE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Amit Gaur » Convert String to a Table using CTE

74 posts, Page 1 of 8

1 2 3 4 5 »»»

Convert String to a Table using CTE

Rate Topic

Display Mode

Topic Options

Author

Message

agaur-588016

Posted Saturday, December 12, 2009 11:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 08, 2010 1:19 PM
Points: 5, Visits: 46

Comments posted to this topic are about the item Convert String to a Table using CTE

Post #833380

samir-424465

Posted Monday, December 14, 2009 12:28 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 3:13 AM
Points: 98, Visits: 68

The approach mentioned using CTE does not work for string (@array) which has more than 100 numbers. (e.g '1,2,3,4,5,....,101'). SQL Server returns following error message -
"Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

In order to overcome this error following query hint need to be used in Select statement with max value of recursion -

option (MAXRECURSION 32767)

Note : This is not a generic solution, since the query will fail again if the string (@array) has more than 32767 numbers which form a comma separated string.

Post #833629

mylesjj

Posted Monday, December 14, 2009 12:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 12, 2012 11:06 AM
Points: 5, Visits: 31

This would fail if you have only one item in the @array var without a trailing comma e.g:

DECLARE @array VARCHAR(max)
SET @array = '1'
SELECT item FROM strToTable(@array,',')

This would break the SUBSTRING statements. The function requires that there is a trailing delimiter e.g:

DECLARE @array VARCHAR(max)
SET @array = '1,'
SELECT item FROM strToTable(@array,',')

It would be nice if the function could handle a single item in the @array var without a trailing delimiter.

Myles J

Post #833630

Alexey Eliseev

Posted Monday, December 14, 2009 12:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 22, 2012 8:11 AM
Points: 9, Visits: 21

Another variant

create FUNCTION [dbo].[ft_ConvertStrToTable](
@Str varchar(8000), -- строка с разделителями
@sDiv char(1) -- символ разделителя
)
RETURNS
@ConvertStrToTable TABLE
(
sValue varchar(255)
)
AS
BEGIN

Declare @Pos int, @PosPrev int;
set @Str = @Str+@sDiv;

set @Pos = 1;
set @PosPrev = @Pos;
while 1=1
begin
set @Pos = CHARINDEX(@sDiv, @Str, @PosPrev);
if @Pos = 0
break;

insert into @ConvertStrToTable (sValue)
values(substring(@Str, @PosPrev, @Pos-@PosPrev));

set @PosPrev = @Pos+1;
end;

RETURN
END

Post #833631

msaleem-583379

Posted Monday, December 14, 2009 12:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 09, 2011 12:33 AM
Points: 2, Visits: 16

Interesting idea indeed! However there are obviously some limitations (maxrecursion being one of those), plus the performance 'may' not be up to the mark.

The best method to split a integer CSV to a string (w.r.t. performance) is the 'crude' one i.e. finding and extracting integers one by one (see below sample user defined function). We have compared various approaches and found this method to be most performing of all the implementations.

Create Function dbo.udf_1 (
@csv varchar(max)
)
Returns @tbl Table (IntValue int)
As
Begin
If( @csv Is Null Or Len(@csv) = 0 )
Return

Declare @iStartPosition int, @iEndPosition int,
@vcTmpId varchar(15), @cDelimiter char(1)

Select @iStartPosition = 1, @cDelimiter = ',', @iEndPosition = charindex( @cDelimiter, @csv )

While @iEndPosition <> 0
Begin
Select @vcTmpId = substring(@csv, @iStartPosition, @iEndPosition - @iStartPosition)
Select @iStartPosition = @iEndPosition + 1

Insert Into @tbl Values( @vcTmpId )

Select @iEndPosition = charindex( @cDelimiter, @csv, @iStartPosition )
End

Select @vcTmpId = substring(@csv, @iStartPosition, Len(@csv) - @iStartPosition + 1)
Insert Into @tbl Values( @vcTmpId )

Return
End
Go

-- Usage
Select * From dbo.udf_1('1,2,2342,3534,46546,4354,22')
Go

Post #833632

matt32

Posted Monday, December 14, 2009 1:29 AM

Valued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 5:32 AM
Points: 61, Visits: 171

with the hint

OPTION(MAXRECURSION 0)

no limits for recursion.

the problem of the recursion error should be solved ...

Post #833645

sam.walker

Posted Monday, December 14, 2009 1:59 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 2:14 AM
Points: 14, Visits: 93

Ok.

And I've got a really quick one for this solution if you start dealing with large numbers of items.

Instead of comma separating as they get passed in, turn them into 10 char padded with spaces, such that the

1st id stored from chars 1-10
2nd from 11-20
and so on

then the built string gets passed to the proc as that

then you can use a tally table to break it up in one simple command

select Id = substring(@idlist, t.n * 20 - 19, t.n * 20), idx= n
From dbo.tally where n < (len(@idlist)+19)/20

Post #833652

Wilfred van Dijk

Posted Monday, December 14, 2009 2:02 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 12:56 AM
Points: 898, Visits: 1,045

This CTE method is not fool proof: it doesn't work if the separator character is not in the main string:

Msg 536, Level 16, State 5, Line 3
Invalid length parameter passed to the SUBSTRING function.

Wilfred
The best things in life are the simple things

Post #833654

werner.broser

Posted Monday, December 14, 2009 2:06 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 05, 2012 3:10 AM
Points: 101, Visits: 166

Hi,

even a trailing delimiter would not work correct

eg. select * from strtotable('1,',','),

because then the result is 1 and 0 !

kr/Werner

Post #833657

Roland Howard Boorman

Posted Monday, December 14, 2009 2:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 30, 2010 3:50 AM
Points: 3, Visits: 21

I simply use XML.
Using XML whole complex structures can be passed to SQL and treated as Tables.
You can use Functions with the XML to render these into virtual tables and treat these as regular tables!

Post #833658

« Prev Topic | Next Topic »

74 posts, Page 1 of 8

1 2 3 4 5 »»»

Permissions