The relationship between table-valued function and the split () in JS

In the company with the cloud platform to do development is trouble, do a lot of functions or some harvest, have no way to write a blog, the result went home to the brain to remember the written out.

split () This function is not unfamiliar to us, but the current station has a lot of fields and then randomly tick the parameters passed to the background to do processing, it is troublesome, we need to pass these as strings to the stored procedure, In the stored procedure to separate these strings into individual individuals, I do not say that the array, because the stored procedure does not have an array of this said.

This is when we think of table-valued functions. The table-valued function returns a table of a table type . Speaking of which I think a lot of people think, this is not an array form? A table is an array, each row is a value in an array, but how does the value inside it traverse or take out? I think we can go through it in the form of a cursor .

Before I did a Gompertz algorithm model , the VBA writes the algorithm, all uses the stored procedure writes out, inside involves many one array, two dimensional array. I wrote it with a virtual table.

Now let's do it!

Here, let's first ignore the inline table-valued function or the multi-statement table-valued function .

// syntax structure, the function body consists of a return statement, the last call is the same as tabular CREATE FUNCTION [schema_name.] Function_name ([{@parameter_name [as] [Type_schema_name.] Parameter_data_type< c3/>default  ]}     [,... n]  ] RETURNS TABLE    <function_option> [,... N]]< c10/>[as]    RETURN [(] select_stmt [)] [;]

This is more abstract, now a specific use of the function.

CREATE FUNCTIONSplit (@Text NVARCHAR(4000),@Sign NVARCHAR(4000))  RETURNS  @tempTable TABLE(IDINT IDENTITY(1,1)PRIMARY KEY,[VALUE] NVARCHAR(4000))   as  BEGIN      DECLARE @StartIndex INT                --where to start looking    DECLARE @FindIndex  INT                --location found    DECLARE @Content    VARCHAR(4000)--the value found    SET @StartIndex = 1  　　                     SET @FindIndex=0            --start loop lookup string comma     while(@StartIndex <= LEN(@Text))      BEGIN          SELECT @FindIndex = CHARINDEX(@Sign,@Text,@StartIndex)          IF(@FindIndex =0 OR @FindIndex  is NULL)          BEGIN          --if we don't find them , we're done.            SET @FindIndex = LEN(@Text)+1          END           SET @Content = LTRIM(RTRIM(SUBSTRING(@Text,@StartIndex,@FindIndex-@StartIndex)))          --Initialize the location of the next lookup        SET @StartIndex = @FindIndex+1          --Insert the value you are looking for in the table type that you want to return        INSERT  into @tempTable([VALUE])VALUES(@Content)       END      RETURN  END  -------------------------------------------------------------------SELECT *  fromDbo. Split ('a,b,c,d,e,f,g',',')

There are 5 functions involved in this.

The first:LEN () This is not to say, is similar to JS in length

Second:LTRIM () this removes whitespace or other predefined characters from the left side of the string

Third:RTRIM () the end of the string begins to delete white space characters or other predefined characters

Fourth: CHARINDEX (expression1, Expression2, [start_location]) The return value is int The function is a bit of a meaning, and it's also important.

• The first parameter is the string to be searched;
• The second parameter is where to find the string;
• The third parameter is the location where the search begins;

This function is similar to the Startwith (), Indexwith () functions in C #.

Example:customname contains the first name and last name of the customer, separated by a space. We use the CHARINDX function to determine the position of the middle space of two names. With this method, we can parse the space position of the ContactName column so that only the last name part of the column can be displayed.

Fifth: SUBSTRING (str, POS, len) This function is similar to JS in the SUBSTRING () is used to grab a field in the data part of a/intercept a string of a method.

• The first parameter is a string to intercept;
• The second parameter is the starting position;
• The third parameter is the length of the Intercept

Example: SELECT SUBSTR (Store_name, 2, 4) from Geography WHERE store_name = ' San Diego ';

The relationship between table-valued function and the split () in JS