Solved

# TSQL for Table-Valued Function

Posted on 2015-01-19
229 Views
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
Question by:AXISHK
• 3
• 3
• 2

LVL 24

Expert Comment

#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

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

No - Split is different to dbo.split.

You could change

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

to

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

and still get the right answer.

The fact that the function is called dbo.Split doesn't make any difference.
0

LVL 48

Expert Comment

>>"#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

0

LVL 48

Expert Comment

>>" 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

0

Author Comment

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

Phillip Burton earned 200 total points
0

LVL 48

Accepted Solution

PortletPaul earned 300 total points
?... 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
``````
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 |
``````
0

## Featured Post

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…