It is often used to split a string into a table table, where SQL table-valued functions and CLR table-valued functions are implemented in two ways:
SELECT * from FP_EMSDB_PUB.dbo.SqlSplit ('/','1oz/1.5oz/2oz/3oz')
The effect is as follows:
SQL implementation table-valued functions
CREATE FUNCTION [dbo].[Splitstr](@s varchar(Max),--string to be split@split varchar(Ten)--Data Separators)RETURNS @re TABLE(IDint IDENTITY(1,1), colvarchar(Max))--Create a temporary table save the split character asBEGIN DECLARE @splitlen int SET @splitlen=LEN(@split+'a')-2 while CHARINDEX(@split,@s)>0 BEGIN INSERT @re VALUES( Left(@s,CHARINDEX(@split,@s)-1)) SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,"') END INSERT @re VALUES(@s) RETURNEND
To create a CLR table-valued function
--registry value function Sqlsplit CREATE FUNCTION[dbo].[Sqlsplit](@separator NVARCHAR( +),@string NVARCHAR(MAX)) RETURNS TABLE(SerialNumberINT, StringValueNVARCHAR(1024x768) ) with EXECUTE asCALLER--for permission checking of referenced objects when the user executes a function asEXTERNAL NAME[sqlfunctionassembly].[sqlclr.sqlfunction].[Sqlsplit] --EXTERNAL Name Assembly name. Class name. Method Name GO
CLR implementation table-valued functions
Public Partial classSqlFunction {/// <summary> ///SQL Server String Segmentation method/// </summary> /// <param name= "separator" ></param> /// <param name= "pendingstring" ></param> /// <returns></returns>[Microsoft.SqlServer.Server.SqlFunction (DataAccess=Dataaccesskind.read, IsDeterministic=true, Name="Sqlsplit", FillRowMethodName="Sqlsplit_fillrow", TableDefinition="serialnumber int,stringvalue nvarchar (1024x768)")] Public StaticIEnumerable sqlsplit (SqlString separator, SqlString pendingstring) {string_separator =string. Empty; string_pendingstring =string. Empty; if(Pendingstring.isnull)return NULL; _pendingstring=pendingstring.tostring (); if(string. IsNullOrEmpty (_pendingstring))return NULL; _separator= separator. IsNull?",": Separator. ToString (); _separator=string. IsNullOrEmpty (_separator)?",": _separator; string[] STRs = _pendingstring.split (New string[] {_separator}, stringsplitoptions.removeemptyentries); List<ResultData> resultdatalist =NewList<resultdata>(); for(inti =0; I < STRs. Length; i++) {Resultdatalist.add (NewResultdata (i +1, Strs[i])); } returnresultdatalist; } /// <summary> ///Populating data Methods/// </summary> /// <param name= "obj" ></param> /// <param name= "serialnumber" ></param> /// <param name= "StringValue" ></param> Public Static voidSqlsplit_fillrow (Object obj, outSqlInt32 SerialNumber, outSqlString StringValue) {Resultdata Resultdata=(resultdata) obj; SerialNumber=Resultdata.serialnumber; StringValue=Resultdata.stringvalue; } /// <summary> ///Defining return Types/// </summary> Public classResultdata {/// <summary> ///serial number, No./// </summary> PublicSqlInt32 SerialNumber {Get;Set; } /// <summary> ///each sub-string after splitting/// </summary> PublicSqlString StringValue {Get;Set; } PublicResultdata (SqlInt32 serialnumber, SqlString stringvalue) {serialnumber=serialnumber; StringValue=StringValue; } } }
PCB MS SQL table-valued functions vs. CLR table-valued Functions (example: string split-table)