Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Convert String to a Table using CTE Expand / Collapse
Author
Message
Posted Saturday, December 12, 2009 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 8, 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
Posted Monday, December 14, 2009 12:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 15, 2013 10:10 PM
Points: 107, Visits: 82
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
Posted Monday, December 14, 2009 12:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 3:13 AM
Points: 5, Visits: 36
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
Posted Monday, December 14, 2009 12:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, December 14, 2009 12:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 9, 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
Posted Monday, December 14, 2009 1:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Monday, December 14, 2009 1:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 4, 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
Posted Monday, December 14, 2009 2:02 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:59 AM
Points: 938, Visits: 1,156
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
Posted Monday, December 14, 2009 2:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 5, 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
Posted Monday, December 14, 2009 2:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 2:03 AM
Points: 12, Visits: 45
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse