This post is a part of Mohd Nizamuddin very interesting article
Sending multiple rows to the Database from an Application
Albert
Table valued function using Number List
First I will explain the pieces of code, which form the building blocks of the final table valued function.
We need to create a number list using the CTE as below
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
SELECT * FROM Numbers
This CTE is creating the list of numbers from 1 to POWER(POWER(POWER(POWER(2, 2), 2), 2), 2), i.e. until 65536.
Now Consider the below code snippet, where @list and @delim variables have been assigned.
DECLARE
@list NVARCHAR(MAX), @delim NCHAR(1)
SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','
;WITH
L0 AS(SELECT 1 AS
c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
SELECT
@list List,
SUBSTRING(@list, Number, CHARINDEX(@delim,
@list + @delim, Number) - Number) AS Value,
Number AS StartingFrom,
CHARINDEX(@delim, @list + @delim, Number) AS DelimeterPosition
FROM Numbers
WHERE Number <= CONVERT(INT, LEN(@list))
AND SUBSTRING(@delim + @list, Number,
1) = @delim
The SUBSTRING statement, cuts characters from @list starting from character position (1, 5, 11 and 16).
SUBSTRING(@list, Number, CHARINDEX(@delim, @list + @delim, Number) - Number)
The number of characters to be cut is decided by CHARINDEX which will
return 4, 10, 15, 20 in each row, where it finds the delimiter character.
CHARINDEX(@delim, @list + @delim, Number) - Number
The above SELECT only works until the number of characters present in the
@list variable due to the condition
Number <= CONVERT(INT, LEN(@list))
The duplicate values are filtered out from the output list by the "WHERE" condition created
using the SUBSTRING function which will only return a value when it finds the delimiter
SUBSTRING(@delim + @list, Number, 1) = @delim
The output of the code snippet above would be:
| List | Value | Starting From | Delimiter Position |
| aaa,bbbbb,cccc,dddd | aaa | 1 | 4 |
| aaa,bbbbb,cccc,dddd | bbbbb | 5 | 10 |
| aaa,bbbbb,cccc,dddd | cccc | 11 | 15 |
| aaa,bbbbb,cccc,dddd | dddd | 16 | 20 |
Table valued function using Numbered List: Implementation
Now combining all the above explained pieces of SQL, we create our
table valued function which will parse the string and return a table having two columns viz. ID and Data.
CREATE FUNCTION [dbo].[TableFormDelimetedString]
(
@param NVARCHAR(MAX),
@delimeter NCHAR(1)
)
RETURNS @tmp TABLE
(
ID INT
IDENTITY
(1,
1),
Data Varchar(MAX)
)
BEGIN
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
INSERT INTO
@tmp (Data)
SELECT
LTRIM(RTRIM(CONVERT(NVARCHAR(4000),
SUBSTRING(@param, Number,
CHARINDEX(@delimeter, @param + @delimeter, Number) - Number)
))) AS Value
FROM Numbers
WHERE Number <=
CONVERT(INT, LEN(@param))
AND SUBSTRING(@delimeter+ @param, Number, 1) = @delimeter
RETURN
END
Table valued function using Numbered List: Usage
So if we now invoke the above function like
SELECT * FROM [TableFormDelimetedString]('Andy:Roger:Thomas:Rob:Victor',':')
We will obtain the following result set:
| ID | Data |
| 1 | Andy |
| 2 | Roger |
| 3 | Thomas |
| 4 | Rob |
| 5 | Victor |
Table valued function using recursive CTE
Here again I will first explain the pieces of code,which form
the building blocks of the final table valued function.
As we know, in a recursive CTE, we have one anchor part and one recursive part.
But if we create a CTE having only the anchor part it would look something like
DECLARE
@list NVARCHAR(MAX), @delim NCHAR(1)
SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim
= ','
;WITH CTETable (start, stop) AS
(
SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list +@delim, 1)
)
SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list,
start,
CASE
WHEN
stop > 0
THEN
stop - start
ELSE
0
END
)))
AS Data
start AS StartingFrom, stop
AS DelimiterPosition
FROM CTETable
The output of the SQL above will be like
| List | Value | Starting From | Delimiter Position |
| aaa,bbbbb,cccc,dddd | aaa | 1 | 4 |
Now by adding a recursive member to the above CTE, which iterates over the stop variable, the SQL looks like
DECLARE
@list NVARCHAR(MAX),
@delim NCHAR(1)
SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','
;WITH CTETable (start, stop)
AS
(
SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list +@delim, 1)
UNION ALL -- added for recursive part of CTE
SELECT start = stop + 1, stop = CHARINDEX(@delim, @list +
@delim, stop+ 1) FROM CTETable WHERE
stop > 0 -- added for recursive part of CTE
)
SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list,
start,
CASE
WHEN stop > 0
THEN stop - start
ELSE 0
END
))) AS Data
start AS StartingFrom, stop AS DelimiterPosition
FROM CTETable
WHERE stop > 0
And gives the following result set
| List | Value | Starting From | Delimiter Position |
| aaa,bbbbb,cccc,dddd | aaa | 1 | 4 |
| aaa,bbbbb,cccc,dddd | bbbbb | 5 | 10 |
| aaa,bbbbb,cccc,dddd | cccc | 11 | 15 |
| aaa,bbbbb,cccc,dddd | dddd | 16 | 20 |
Table valued function using recursive CTE: Implementation
Finally we create a table valued function from the above code blocks, which looks like
CREATE FUNCTION [dbo].[TableFormDelimetedStringWithoutNumberList]
(@list NVARCHAR(MAX),
@delim NCHAR(1) = ','
)
RETURNS @tmp TABLE
(
ID INT IDENTITY (1, 1),
Data Varchar(MAX)
)
BEGIN
;WITH CTETable (start, stop)
AS
(
SELECT start = CONVERT(bigint, 1),
stop = CHARINDEX(@delim, @list + @delim)
UNION ALL -- added for recursive part of CTE
SELECT start = stop + 1,
stop = CHARINDEX(@delim, @list + @delim, stop + 1) -- added for recursive part of CTE
FROM CTETable
WHERE stop > 0
)
INSERT INTO @tmp (Data)
SELECT LTRIM(RTRIM(SUBSTRING(@list,
start,
CASE
WHEN stop > 0
THEN
stop - start
ELSE
0
END))) AS Data
FROM CTETable
WHERE stop > 0
RETURN
END
Table valued function using recursive CTE: Usage
So if we now invoke the above function like
SELECT * FROM[TableFormDelimetedStringWithoutNumberList]('Andy:Roger:Thomas:Rob:Victor',':')
We will obtain the following result set:
| ID | Data |
| 1 | Andy |
| 2 | Roger |
| 3 | Thomas |
| 4 | Rob |
| 5 | Victor |
by the SQL server database engine itself, which would definitely be more efficient
than explicit SQL looping code written by a developer.
