SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert String to a Table using CTE


Convert String to a Table using CTE

Author
Message
Pyay Nyein
Pyay Nyein
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 404
Thanks for the good article, Amit. I was looking for a way this morning to find all the dates between start date and end date, and there you go, your article is here. I manage to replicate your script to find all the middle dates. Thanks..

and thanks for other contributors for the 'MAXRECURSION' option hints..


ALTER FUNCTION [dbo].[MiddleDatesToTable]
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS
@listTable TABLE
(
item DATETIME
)
AS
BEGIN

;WITH rep (item, nextday) AS
(
SELECT DATEADD(DD,1,@StartDate) as 'item', DATEADD(DD,2,@StartDate) as 'nextday'
WHERE DATEDIFF(DD, DATEADD(DD,1,@StartDate), @EndDate) > 0

UNION ALL

SELECT nextday as 'item', DATEADD(DD,1,nextday) as 'nextday'
FROM rep
WHERE DATEDIFF(dd,nextday, @EndDate) > 0
)
INSERT INTO @listTable
SELECT item FROM rep option (MAXRECURSION 0)

RETURN
END
Rob Fisk
Rob Fisk
Say Hey Kid
Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)

Group: General Forum Members
Points: 677 Visits: 428
Always good to see different ways of approaching things. Will have to have a look at the issue with the single item here and then maybe set up some performance tests with the examples given.

I also have been using the tally table version.
CREATE FUNCTION dbo.ProcessStringArray (@inputString NVARCHAR(max), @separator NCHAR(1))
RETURNS @output TABLE(arrayItem NVARCHAR(4000))
AS
BEGIN
/**
* Add start and end separators to the Parameter so we can handle single elements
**/
SET @inputString = @separator + @inputString + @separator
INSERT @output
/**
* Join the Tally table to the string at the character level and when we find a separator
* insert what's between that separator and the next one
**/
SELECT SUBSTRING(@inputString,N+1,CHARINDEX(@separator,@inputString,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@inputString)
AND SUBSTRING(@inputString,N,1) = @separator
RETURN
END


Where N is the integer column of the tally table.

Pretty certain I found this based on another article here somewhere but cannot find it to give credit.

_______________________________________________________
Change is inevitable... Except from a vending machine.

msaleem-583379
msaleem-583379
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 16
I've used a function similar to MiddleDatestoTable in my apps, however I followed a different approach.

Create Function dbo.udf_NumbersTable (
@riStartingNumber Int, @riCount Int
)
Returns @tbl Table (SequenceNumber Int)
As
Begin
-- Logic used from http://www.sqlservercentral.com/articles/Datetime+Manipulation/61822/

;With
tblLevel0 As (Select 1 As C Union All Select 1), --2 rows
tblLevel1 As (Select 1 As C From tblLevel0 As A, tblLevel0 As B),--4 rows
tblLevel2 As (Select 1 As C From tblLevel1 As A, tblLevel1 As B),--16 rows
tblLevel3 As (Select 1 As C From tblLevel2 As A, tblLevel2 As B),--256 rows
tblLevel4 As (Select 1 As C From tblLevel3 As A, tblLevel3 As B),--65536 rows
tblLevel5 As (Select 1 As C From tblLevel4 As A, tblLevel4 As B),--4294967296 rows
tblSeq As (Select Row_Number() Over(Order By C) As N From tblLevel5)

Insert Into @tbl( SequenceNumber )
Select N + @riStartingNumber - 1 From tblSeq Where N Between 1 AND @riCount

Return
End
Go

Declare @dtStart DateTime, @dtEnd DateTime
Declare @iDays Int

-- Example values
Set @dtStart = '20091201'
Set @dtEnd = '20091225'

-- Usage
Set @iDays = DateDiff(d, @dtStart, @dtEnd) + 1

Select DateAdd(d, SequenceNumber, @dtStart) As DateValue
From dbo.udf_NumbersTable(0, @iDays)
matt32
matt32
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 172
ok .. replace the dates with variables and create a proc

WITH mycte AS (SELECT cast ('2010-01-01' AS DATETIME) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 < cast ('2011-01-01' AS DATETIME))
SELECT
year(datevalue) as [Year],
month(datevalue) as [Month],
day(datevalue) as [Day],
datename(dw, DateValue) Weekday
from mycte
OPTION (MAXRECURSION 0)
Paul White
Paul White
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61480 Visits: 11397
Liked the article thanks.

But, uh-oh, here we go again with the string-splitting debate...Laugh

Like it or not, the overall best method ever is to use a CLR TVF. It just is.

http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Fatal Exception Error
Fatal Exception Error
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 386
Why should I use this method versus using a good old fashioned Numbers table?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (169K reputation)SSC Guru (169K reputation)SSC Guru (169K reputation)SSC Guru (169K reputation)SSC Guru (169K reputation)SSC Guru (169K reputation)SSC Guru (169K reputation)SSC Guru (169K reputation)

Group: General Forum Members
Points: 169167 Visits: 39544
msaleem-583379 (12/14/2009)
I've used a function similar to MiddleDatestoTable in my apps, however I followed a different approach.

...


You do realize since your routine is a multi-statement TVF that the Quesry Optimizer will always treat the table returned y this function as if it has only one row regardless of the nuumber of rows actually returned?

This means if your function is returning more than a few hundred rows you won't be getting an optimal execution plan.

I discovered this with some AsOF multi-statement TVF's I developed. I had a query linking seven of this TVF together, and after 30 minutes, cancelled the query and modified (actually drop and create) the TVF from multi-statement to in-line TVF. Amazingly, the query then returned in four minutes.

I have to agree with Paul White, here we go with the discussions regarding what is the best string splitting functions. The use of a recursive CTE, however, is not very scalable. If you don't have a CLR TVF, which Paul indicates is the best, the next best is one based on a tally table. I have a function that actually contains its own tally table should you not have one.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jonathan Melo
Jonathan Melo
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 529
Rob Fisk (12/14/2009)
Pretty certain I found this based on another article here somewhere but cannot find it to give credit.



I believe that must be a Jeff Moden article, unless I'm mistaken?
Bill Nicolich
Bill Nicolich
Say Hey Kid
Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)

Group: General Forum Members
Points: 667 Visits: 545
I'd like to do a poll and find out what most people call it - a "tally" table, a "numbers" table, a "helper" table or what. It would be interesting to know.

Maybe there's a geographic trend. a "tally" table sounds like something out of Great Britain - where they ride around on the trolley - while "helper" comes from Texas/Colorado/Tennessee with some cities rated as the friendliest in the U.S. and then maybe straight up "numbers," an ambiguous and somewhat unhelpful name for L.A. and New York City where the people are rated by some as the most unhelpful in the U.S. (Of course we all know these are generalizations that don't hold true for everybody - and where do they come up with these stats anyway? ).

Are there any more names you've heard for the "tally" table?

Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Dean Cochrane
Dean Cochrane
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 641
I called mine the 'Integers' table. Because it's a table of INTs. Also, I used to write code for the lumber industry, where 'Tally' has a specific meaning.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search