"51cto/bbs" ask: SQL is not a string decomposition, a combination of functions??

"51cto/bbs" ask: SQL is not a string decomposition, a combination of functions??

Original address: http://bbs.51cto.com/thread-1133863-1.html

Problem Description:

VB has two very useful string processing functions:

Split (string, delimiter) acts: Breaks the string by the delimiter as the boundary and decomposes the array. Returns: an array of strings.

Join (character array, delimiter) acts: The element in the character array is concatenated as a string with a delimiter as the boundary. Returns: A string.

Ask the teachers, is there a similar function in SQL?

Solution:

How to use SQL Server Function Implementing Split ?

The--split table function splits a string by the specified delimiter and returns a table. Create function split (@string varchar (255),--The string to be split @separator varchar (255)--the delimiter) returns @array table (item varchar (255 )) asbegindeclare @begin int, @end int, @item varchar (255) Set @begin = 1set @end =charindex (@separator, @string, @begin) while (@end <>0) beginset @item = substring (@string, @begin, @[email protected]) insert into @array (item) VALUES (@ Item) Set @begin = @end +1set @end =charindex (@separator, @string, @begin) endset @item = substring (@string, @begin, Len (@ string) [email protected]) if (Len (@item) >0) insert into @array (item) VALUES (substring (@string, @begin, Len (@string) [ Email protected]) Returnend

How to use SQL CLR Implementing Split ?

Step One:

To start, run Visual Studio 2012, select New Project, select Visual C #, class library, and name the class library Fnsplit.

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M01/57/57/wKioL1SYwgmhxSZwAAKuuI0jVgM863.jpg "border=" 0 "height=" 544 "/>

Step Two:

By default, Visual Studio creates an empty class named "Class1.cs" and a right-click Rename to CLRFunction.cs.

650) this.width=650; "title=" clip_image002 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M02/57/5A/wKiom1SYwWCyec8rAABNvKpAZ6A665.jpg "border=" 0 "height=" 121 "/>

Step Three:

Double-click the "CLRFunction.cs" file and enter the following code:

using system;using system.collections;using system.text;using microsoft.sqlserver.server; Using system.data.sqltypes;namespace fnsplit{public static class clrfunctions{//sql  Functions require an additional  "SqlFunction"  attribute.//this attribute  provides sql server with additional meta data information it  needs//regarding our custom function. in this example we are  not accessing any data, and our//function is deterministic. so  we let sql know those facts about our function with//the  dataaccess and isdeterministic parameters of our attribute.//additionally,  SQL needs to know the name of a function it can  defer to when it needs//to convert the object we have returned from our  function into a structure that sql//can understand. this is  provided by the  "FillRowMethodName"  shown below. [SqlFunction (dataaccess = dataaccesskind.none,fillrowmethodname =  "MyFillRowMethod", Isdeterministic=true)]//sql functions must be declared as static. table  valued functions must also//return a class that implements the  ienumerable interface. most built in//.net collections and arrays  already implement this interface.public static ienumerable split (String  stringtosplit, string delimiters) {//one line of c# code splits  Our string on one or more delimiters...//a string array is one of many objects that are returnable  from//a sql clr function - as it implements the required  Ienumerable interface.string[] elements = stringtosplit.split (delimiters. ToCharArray ()); return elements;} sql needs to defer to user code to translate the an  Ienumerable item into something//sql server can understand. in this  case we convert our string to a SqlChar object...public  Static void myfillrowmethod (object theitem, out sqlchars results) {results =  new sqlchars (Theitem.tostring ());}}}

Step Four:

From the Build menu, select "Build Fnsplit". After compiling, the "FnSplit.dll" file is generated in the bin directory. Copy the file to a SQL Server accessible directory, such as D:\MSSQL\DATA\CLRLibraries.

650) this.width=650; "title=" clip_image003 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M02/57/57/wKioL1SYwgqiamdvAACtJwqkf9Q500.jpg "border=" 0 "height=" 208 "/>

650) this.width=650; "title=" clip_image004 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M00/57/5A/wKiom1SYwWGQ2YFcAADAVW8b8wc365.jpg "border=" 0 "height=" 165 "/>

Step Five:

Open SQL Server Management Studio and connect to the instance where you want to deploy the DLL.

650) this.width=650; "title=" clip_image005 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image005 "src=" http://s3.51cto.com/wyfs02/M01/57/5A/wKiom1SYwWKRZyKcAAEMXG_IAeI447.jpg "border=" 0 "height=" 312 "/>

Step Six:

CLR integration is disabled by default in SQL Server. Execute the following command to enable CRL integration.

sp_configure ' show advanced options ', 1reconfiguregosp_configure ' clr enabled ', 1reconfiguregosp_configure ' show Advanced Options ', 0RECONFIGUREGO

Step Seven:

Creates a assemblies in the application's database through the DLL.

Create Assembly fnsplit from ' D:\MSSQL\DATA\CLRLibraries\fnSplit.dll ' with permission_set = Safego

650) this.width=650; "title=" clip_image006 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M00/57/5A/wKiom1SYwWKSYOLWAAK02HMvX6s158.jpg "border=" 0 "height=" 373 "/>

Step Eight:

Create the Split function, which is similar to creating a standard function, in addition to using "External" to locate the actual program logic into your DLL.

Create Function fnsplit (@StringToSplit nvarchar (max), @splitOnChars nvarchar (max)) returns Table (Results nvarchar (max) ) asexternal name Fnsplit. [Fnsplit.clrfunctions]. Split; GO

650) this.width=650; "title=" clip_image007 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image007 "src=" http://s3.51cto.com/wyfs02/M02/57/57/wKioL1SYwhCCIpYCAAJCrHDQ_fA670.jpg "border=" 0 "height=" 332 "/>

Step Nine:

Test the Split function

SELECT * from Dbo.fnsplit (' 1,2,3,4,5:6:7~8~9 ', ',: ~ ')

650) this.width=650; "title=" clip_image008 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image008 "src=" http://s3.51cto.com/wyfs02/M02/57/5A/wKiom1SYwWqjDksLAABnDAooLiI193.jpg "border=" 0 "height=" 225 "/>

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1593531

"51cto/bbs" ask: SQL is not a string decomposition, a combination of functions??