Table value functions
The difference between a Table value function and a scalar value function is that the Table value function returns a Table Type equivalent to a virtual Table stored in memory.
Syntax of the Table value function:
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 I want to write a more practical Table value function ..
Write a table value function for string cutting.
Code
1 -------------------------- this function is used to cut the string -----------------
2 -- the first parameter of the function is the string to be cut. The second parameter is the string to be cut.
3 create function Split (@ Text NVARCHAR (4000), @ Sign NVARCHAR (4000 ))
4 RETURNS @ tempTable TABLE (id int identity (4000) primary key, [VALUE] NVARCHAR ))
5
6 BEGIN
7 DECLARE @ StartIndex INT -- start to find the location
8 DECLARE @ FindIndex INT -- locate
9 DECLARE @ Content VARCHAR (4000) -- value found
10 -- initialize some variables
11 SET @ StartIndex = 1 -- the string SEARCH position in the T-SQL starts from 1
12 SET @ FindIndex = 0
13
14 -- start to search for a string using commas (,)
15 WHILE (@ StartIndex <= LEN (@ Text ))
16 BEGIN
17 -- search for the string function CHARINDEX. The first parameter is the string to be searched.
18 -- where does the second parameter look for this string?
19 -- the third parameter is the start position.
20 -- the return value is the position of the string.
21 SELECT @ FindIndex = CHARINDEX (@ Sign, @ Text, @ StartIndex)
22 -- If no result is found, 0 is returned.
23 IF (@ FindIndex = 0 OR @ FindIndex is null)
24 BEGIN
25 -- if no one is found, it indicates that the search is complete.
26 SET @ FindIndex = LEN (@ Text) + 1
27 END
28 -- truncation string function SUBSTRING the first parameter is the string to be truncated
29 -- the second parameter is the start position.
30 -- the third parameter is the truncation length.
31 -- @ FindIndex-@ StartIndex indicates the location to be searched-start position = length to be intercepted
32 -- The LTRIM and RTRIM functions remove spaces on the left and right of the string.
33 SET @ Content = LTRIM (RTRIM (SUBSTRING (@ Text, @ StartIndex, @ FindIndex-@ StartIndex )))
34 -- initialize the next search location
35 SET @ StartIndex = @ FindIndex + 1
36 -- Insert the value to the Table type to be returned
37 insert into @ tempTable ([VALUE]) VALUES (@ Content)
38 END
39 RETURN
40 END
This function is similar to the Split method of the string class in. Net.
Test this function now.
This function returns the Table type, so you can use the following syntax to call it.
Code
SELECT * FROM dbo. Split ('a, B, c, d, e, f, G ',',')
Result
This function is quite practical ..
If you have any questions, leave a message .....
