MS SQL to create a table-valued function

This is an example of creating a table, I feel very classic, so come up with to share with you

CREATE FUNCTION [dbo]. [SplitToTable]
(
@ RowData nvarchar (2000),
@ SplitOn nvarchar (5)
)
RETURNS @ RtnValue table
(
Id int identity (1,1)
Data nvarchar (100)
)
AS
BEGIN
declare @ Cnt int
Set @ cnt = 1

While (Charindex (@ SplitOn, @ RowData)> 0)
Begin
Insert Into @ RtnValue (data)
Select
Data = ltrim (rtrim (Substring (@ RowData, 1, Charindex (@ SplitOn, @ RowData) -1)))

Set @ RowData = Substring (@ RowData, Charindex (@ SplitOn, @ RowData) +1, len (@ RowData))
Set @ Cnt = @ Cnt + 1
End

Insert Into @ RtnValue (data)
Select Data = ltrim (rtrim (@ RowData))

Return
END

This function can be a special attached delimited string into a table.

For example, the string “test1, test2”

Executed in the database select * FROM dbo.SplitToTable (‘test1, test2’, ‘,’) after the results are as follows:

ID Data

1 test1

2 test2

Problems with the upload pictures today, I’ll just write.