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

Source: Internet
Author: User
Tags how to use sql server mssql how to use sql management studio sql server management sql server management studio

"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??

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.