SQL Server CLR Integration Series 4 Create a CLR Table value function-practical split Function

As we all know, in C #, it is easy to use the split method to divide a string into an array with the specified characters, but in T-SQL, it is .... In my project, there are the following applications: Read a batch of data in batches, then process the data in each row. After processing, return the processed ID and the failed ID to the database, this involves a batch update problem. Do you want to write IDs one by one for batch processing and update them back to the database or open the database once for each ID? In fact, only T-SQL supports arrays. Now let T-SQL support arrays!

Everyone said that my description is not good. I will simply use it this time. Code + Annotation. Using System;
Using System. collections;
Using System. text;
Using System. Data. sqltypes;
Using Microsoft. sqlserver. server;

Namespace Stringsplit
{
Public   Class Stringhelp
{
// This feature defines an SQL Table value function. The table returned by this function is defined as: String nvarchar (200)
// The fillrow method is used to fill the rows in the table.
// Note that this method must return an ienumerable type, which is public and static. The input parameter of this method is the input parameter of the SQL function.
[Sqlfunction (tabledefinition =   " String nvarchar (200) " , Fillrowmethodname =   " Fillrow " )]
Public   Static Ienumerable getstrings ( String X, Char Y)
{
//Returns a string array, which conforms to the ienumerable interface. Of course, you can also return hashtable and other types.
ReturnX. Split (y );
}
// The method for filling the rows in the returned table has certain rules:
// It must be a void type returned by null, and the first input parameter must be an object. All parameters following it must be of the out type.
// The type, number, and order of parameters are determined by the Column Structure of the returned table! (The table structure defined in tabledefinition = "string nvarchar (200)
Public   Static   Void Fillrow ( Object Row, Out   String Splitedstring)
{
//This object is actually an iteration returned by the getstrings (string X, char y) function, so you can assign a value to the column directly.
Splitedstring=(String) row;
}
}
}

In this way, compile the library and use the following SQL statement to create the DLL into the database:
Create Assembly arrayassembly
From 'd: \ stringsplit. dll'
With permission_set = safe;
Change the address of the DLL.
Then, if it is the first time for CLR integration application, you need to enable the CLR function by using the following statement:
Exec sp_configure 'clr enabled', '1 ';
Go
Reconfigure;
Go

You can create the table value function by using the following statement:
Create Function splitstring (@ x nvarchar (200), @ Y nchar (1 ))
Returns table (stringelement nvarchar (200 ))
As external name arrayassembly. [stringsplit. stringhelp]. getstrings;
Go
Note the naming rules for this statement.Article.
Then you can test the table value function.

Select * From splitstring ('xiangxiang, zangjifei ',',')

return result:
This makes it easy to use.
For example:
select * From DBO. t_test as t where T. ID in (select * From splitstring ('12, 23', ',')