"51cto/bbs" ask: SQL has no string combination join function??

"51cto/bbs" ask: SQL has no string combination join function??

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 Implement join ?

--Using a user-defined function to complete the string merge process with SELECT processing--processed data create TABLE TB (col1 varchar), col2 int) INSERT TB SELECT ' A ', 1UNION all Select ' A ', 2UNION all select ' B ', 1UNION all select ' B ', 2UNION all select ' B ', 3go--merge handler function create function dbo.f_str (@col1 VA Rchar (RETURNS) asbegindeclare @re varchar SET @re = ' SELECT @[email protected]+ ', ' +cast (col2 as varchar) from Tbwhere [email Protected]return (STUFF (@re, ")") endgo--Call function Select Col1,col2=dbo.f_str (col1) from TB GROUP by col1--Delete Test drop TABLE tbdrop FUNCTION f_str/*--result col1 col2---------------------a 1,2b 1,2,3--*/go

How to use SQL CLR Implement join ?

Step One:

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

650) this.width=650; "title=" clip_image002 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M01/57/64/ Wkiol1szog6ri1e7aaeejbfg5bg424.jpg "border=" 0 "height=" "width=" 558 "/>"

Step Two:

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

650) this.width=650; "title=" clip_image003 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M02/57/64/ Wkiol1szog6tippbaabe9wj9ake778.jpg "border=" 0 "height=" 117 "width=" 236 "/>

Step Three:

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

Using system;using system.data;using microsoft.sqlserver.server;using system.data.sqltypes ;using system.io;using system.text; [Serializable] [Sqluserdefinedaggregate (Format.userdefined, //use custom serialization to serialize  the intermediate resultIsInvariantToNulls = true, //optimizer  propertyisinvarianttoduplicates = false, //optimizer propertyisinvarianttoorder = &NBSP;FALSE,&NBSP;//OPTIMIZER&NBSP;PROPERTYMAXBYTESIZE&NBSP;=&NBSP;-1)  //maximum size in  bytes of persisted value]public class concatenate : ibinaryserialize{///  <summary>/// the variable that holds the intermediate result  of the concatenation/// </summary>private StringBuilder  Intermediateresult;/// <summary>/// initialize the internal data sTructures/// </summary>public void init () {this.intermediateresult = new  StringBuilder ();}  <summary>/// Accumulate the next value, not if the  Value is null/// </summary>/// <param name= "Value" ></param>///  <param name= "Separator" ></param>public void accumulate (SqlString value,  sqlstring separator) {if  (value. IsNull) {return;} This.intermediateResult.Append (value. Value). Append (separator);}  <summary>/// Merge the partially computed aggregate with  This aggregate./// </summary>/// <param name= "Other" ></param>public  void merge (concatenate other) {this.intermediateResult.Append (other.intermediateresult);}  <summary>/// Called at the end of aggregation, to  Return the results of the aggregation./// </summary>/// <returns></ Returns>public sqlstring terminate () {string output = string. empty;//delete the trailing comma, if anyif  (this.intermediateresult !=  null&& this.intermediateresult.length > 0) {output =  This.intermediateResult.ToString (0, this.intermediateresult.length - 1);} return new sqlstring (output);} Public void read (binaryreader r) {Intermediateresult = new stringbuilder (R. ReadString ());} Public void write (binarywriter w) {w.write (this.intermediateResult.ToString ());}}

Step Four:

From the Build menu, select "Build Fnconcatenate". After compiling, the "FnConcatenate.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_image004 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image004 "src=" Http://s3.51cto.com/wyfs02/M00/57/64/wKioL1SZOhOw_1p_ Aacqw8u0yr8545.jpg "border=" 0 "height=" 197 "width=" 370 "/>

650) this.width=650; "title=" clip_image006 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M01/57/64/ Wkiol1szohoacgumaacri8knpz0986.jpg "border=" 0 "height=" 177 "width=" 558 "/>

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_image007 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image007 "src=" http://s3.51cto.com/wyfs02/M02/57/64/wKioL1SZOhXy5XqJAAEMXG_ Iaei092.jpg "border=" 0 "height=" 312 "width=" 420 "/>

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.

Use advantureworks2012gocreate assembly fnconcatenatefrom N ' D:\MSSQL\DATA\CLRLibraries\fnConcatenate.dll ' with Permission_set = Safego

650) this.width=650; "title=" clip_image009 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image009 "src=" Http://s3.51cto.com/wyfs02/M02/57/66/wKiom1SZOW6ANdNDAADZKXp_ C7q409.jpg "border=" 0 "height=" 247 "width=" 558 "/>

Step Eight:

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

Create aggregate concatenate (@value nvarchar (max), @separator nvarchar (ten)) returns nvarchar (max) external name Fnconcatenate.concatenatego

650) this.width=650; "title=" clip_image011 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image011 "src=" http://s3.51cto.com/wyfs02/M00/57/66/ Wkiom1szoxdwrvluaadyg5fy-ok596.jpg "border=" 0 "height=" 247 "width=" 558 "/>

Step Nine:

Testing the CONCATENATE function

Select Dbo.concatenate (FirstName, ', ') from Person.person

650) this.width=650; "title=" clip_image012 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image012 "src=" http://s3.51cto.com/wyfs02/M01/57/66/ Wkiom1szoxcyosibaafc4ze3el8411.jpg "border=" 0 "height=" 417 "width=" 526 "/>

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

"51cto/bbs" ask: SQL has no string combination join function??