August 27, 2008 at 5:23 am
CREATE FUNCTION [dbo].[Split](@string varchar(1500), @delimiter char(1))
RETURNS TABLE
AS
RETURN
(
WITH Pieces(ID, Start, Stop) AS
(
SELECT 1, 1, CHARINDEX(@delimiter, @string) WHERE @string IS NOT NULL AND ltrim(rtrim(@string))<>''
UNION ALL
SELECT ID + 1, Stop + 1, CHARINDEX(@delimiter, @string, Stop + 1) FROM Pieces WHERE Stop > 0
)
SELECT ID, SUBSTRING(@string, Start, CASE WHEN Stop > 0 THEN Stop-Start ELSE 1500 END) AS Items FROM Pieces
)
This function will use only 100 times recursive loop, I need to have looping more that 100 times, any solaution to the same?
I tried to use another ready made function , which takes huge time…as per performance wise
….another sample is:
CREATE FUNCTION dbo.Split ( @vcDelimitedString varchar(8000),
@vcDelimiter varchar(100) )
RETURNS @tblArray TABLE
(
ElementID smallint IDENTITY(1,1), --Array index
Element varchar(1000) --Array element contents
)
AS
BEGIN
DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint
SET @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
please help with some suggestion
SID
August 27, 2008 at 5:32 am
Hi there,
TRy creating a function that uses a Tally table.
Here is my solution:
CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(100) ,
@Delimiter CHAR(1)
)
RETURNS @Results TABLE
(ID INT IDENTITY(1,1),
String VARCHAR(100))
AS
BEGIN
INSERT INTO @Results
SELECT SUBSTRING(@String+@Delimiter, n,
CHARINDEX(@Delimiter, @String+@Delimiter, n) - n)
FROM tally
WHERE n <= LEN(@String)
AND SUBSTRING(@Delimiter + @String,
n, 1) = @Delimiter
ORDER BY n
RETURN
END
GO
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2008 at 5:35 am
August 27, 2008 at 6:03 am
thanks a lot for the same.
SID
August 29, 2008 at 1:50 am
Although the tally table solution is likely to be much more efficient for this particular task, for completeness I'll add that the way to control the limit on levels of recursion in a CTE is with the MAXRECURSION option.
When MAXRECURSION isn't specified, the default value is 100.
To remove the limit, set MAXRECURSION to 0.
The following example sets the MAXRECURSION to 10:
WITH recursiveCTE AS
(
SELECT ...
UNION ALL
SELECT ...
)
SELECT ...
FROM recursiveCTE
OPTION (MAXRECURSION 10);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply