Custom table-valued functions in the database

See other people write functions, a start to understand, so on the Internet to find information, the original is a table-valued function, the feeling has learned, full of fulfilling sense, excerpt as follows:

Table-Valued functions

The difference between a table-valued function and a scalar-valued function is that a table-valued function returns a table-type table-type equivalent to a single virtual table stored in memory.

Syntax for table-valued functions:

CREATE FUNCTION [schema_name.] Function_name
([{@parameter_name [as] [Type_schema_name.] Parameter_data_type
[= Default]}
[,... N]
]
)
RETURNS TABLE
[With <function_option> [,... N]]
[As]
RETURN [(] select_stmt [)]
[ ; ]

Now write a more useful table-valued Function:

Write a table-valued function that cuts a string

1--------------------------This function is used to cut strings-----------------
2--function parameters The first is the string to be cut the second is what string to cut
3 CREATE FUNCTION Split (@Text NVARCHAR (4000), @Sign NVARCHAR (4000))
4 RETURNS @tempTable TABLE (id INT IDENTITY (PRIMARY) Key,[value] NVARCHAR (4000))
5 as
6 BEGIN
7 DECLARE @StartIndex INT--Where to start looking
8 DECLARE @FindIndex INT--Location found
9 DECLARE @Content VARCHAR (4000)--found value
10--Initialize a number of variables
One SET @StartIndex = 1--t-sql The Find position of the string is starting from 1
SET @FindIndex =0
13
14--Start loop to find the string comma
(@StartIndex <= LEN (@Text))
BEGIN
17--Find String function CHARINDEX The first argument is the string to find
18--The second parameter is where to find the string
19--The third parameter is where you start looking
20--The return value is where the string is found
SELECT @FindIndex = CHARINDEX (@Sign, @Text, @StartIndex)
22--To determine if there is no found return 0
IF (@FindIndex =0 OR @FindIndex is NULL)
BEGIN
25--If you don't find it, you're done.
SET @FindIndex = LEN (@Text) +1
END
28--intercept string function SUBSTRING The first argument is the string to intercept
29--The second parameter is the starting position
30--The third parameter is the length of the Intercept
[Email protected] @StartIndex the location to find-the location to start = the length to intercept
The--ltrim and RTRIM are the whitespace functions that remove the left and right side of the string
SET @Content = LTRIM (RTRIM (SUBSTRING (@Text, @StartIndex, @[email protected]))
34--Initialize the location of the next lookup
SET @StartIndex = @FindIndex +1
36--Inserts the value of the search into the table type to be returned
PNS INSERT into @tempTable ([VALUE]) VALUES (@Content)
END
RETURN
END

This function is similar in effect. The split method of the string class in net

Now, test this function.

This function returns the table type, so you can use the following syntax to invoke the

SELECT * FROM dbo. Split (' a,b,c,d,e,f,g ', ', ')

The results obtained

This function is still more practical.

Original website: http://www.cnblogs.com/chenliang0724/archive/2008/09/26/1298849.html

Custom table-valued functions in the database