Solved

TSQL for Table-Valued Function

Posted on 2015-01-19
8
242 Views
Last Modified: 2015-01-20
What does the following part ctually perform ??

#1  
  WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )

#2
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),

#3
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)


------------------------------------------------------
CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO
0
Comment
Question by:AXISHK
  • 3
  • 3
  • 2
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40557700
#1 is a CTE. It splits the first string into separate rows based on the second string, and gives the position of the start of the split and the end.

#2 identifies the row number. The OVER(ORDER BY (SELECT 1)) is because you have to put something in the OVER, and that just means "I don't care about the order".

#3 converts the positions identified in #1 and creates the actual strings based on those positions.
0
 

Author Comment

by:AXISHK
ID: 40557747
Tks, still have problem,

Function name is "Split', select table name is 'Split'. Is the function calling itself recursively ??

Tks
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40557753
No - Split is different to dbo.split.

You could change

 WITH Split(stpos,endpos)
...
            FROM Split
...
    FROM Split

Open in new window


to

 WITH Hello(stpos,endpos)
...
            FROM Hello
...
    FROM Hello

Open in new window


and still get the right answer.

The fact that the function is called dbo.Split doesn't make any difference.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40559150
>>"#1 is a CTE" (?)

a CTE is a "Common Table Expression"
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
http://msdn.microsoft.com/en-AU/library/ms175972.aspx

No points please.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40559158
>>" Is the function calling itself recursively ??"

The function is not being called recursively, but the CTE is calling itself recursively (which is allowed)
... A common table expression can include references to itself. This is referred to as a recursive common table expression.
http://msdn.microsoft.com/en-AU/library/ms175972.aspx

No points please.
0
 

Author Comment

by:AXISHK
ID: 40559185
I can't get the idea  in #1

select 0 as stops, CHARINDEX(',' , 'Duckman, Cornfed, Ajax, Charles, Mambo') AS endpos       

it will return :
stops     endpos
0             8
About the second one, how does it iterate from the Split  ? can I stimulate it in Management Studio ? Tks
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
             FROM Split
             WHERE endpos > 0
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 200 total points
ID: 40559368
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 40559398
?... Typically one introduces the full name then resorts to the abbreviation.
---------------
For this recursive CTE:

{+edit} Don't forget that the CHARINDEX() function uses the optional third parameter to establish where to start from. This increases at each iteration.

On the first iteration
select 0 as stops, CHARINDEX(',' , 'Duckman, Cornfed, Ajax, Charles, Mambo') AS endpos      
it will return :
stops     endpos
0             8
It then enters the second iteration
SELECT endpos+1, CHARINDEX(',' , 'Duckman, Cornfed, Ajax, Charles, Mambo',endpos+1) AS endpos  
which returns
stops     endpos
9             17           -- the comma after Cornfed
It then enters the third iteration
SELECT endpos+1, CHARINDEX(',' , 'Duckman, Cornfed, Ajax, Charles, Mambo',endpos+1) AS endpos  
which returns
stops     endpos
18             23           -- the comma after Ajax
It then enters the fourth iteration
SELECT endpos+1, CHARINDEX(',' , 'Duckman, Cornfed, Ajax, Charles, Mambo',endpos+1) AS endpos  
which returns
stops     endpos
24             32           -- the comma after Charles
It then enters the fifth iteration
SELECT endpos+1, CHARINDEX(',' , 'Duckman, Cornfed, Ajax, Charles, Mambo',endpos+1) AS endpos  
which returns
stops     endpos
33             0           -- there is no comma after position 33 of the string
Now the endpos is zero the iterations stop due to the where clause:

             WHERE endpos > 0

You can run this as a query to see the effect.
declare @String NVARCHAR(4000) = 'Duckman, Cornfed, Ajax, Charles, Mambo'
declare @Delimiter NCHAR(1) = ','

    ;WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT
        'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
      , stpos
      , endpos
    FROM Split

Open in new window

which produces:
| ID |     DATA | STPOS | ENDPOS |
|----|----------|-------|--------|
|  1 |  Duckman |     0 |      8 |
|  2 |  Cornfed |     9 |     17 |
|  3 |     Ajax |    18 |     23 |
|  4 |  Charles |    24 |     32 |
|  5 |    Mambo |    33 |      0 |

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question