【51CTO/BBS】请教: SQL里有没有字符串组合Join的函数??

【51CTO/BBS】请教: SQL里有没有字符串组合Join的函数??

原帖地址:http://bbs.51cto.com/thread-1133863-1.html

 

问题描述:


VB 中有两个非常好用的字符串处理函数:

Split(字符串,分隔符)作用:将【字符串】以【分隔符】作为边界,分解成数组。 返回:一个字符串数组。

Join(字符数组,分隔符)作用:将【字符数组】中的元素,以【分隔符】作为边界,连接成一个字符串。返回:一个字符串。

请教老师们,SQL里是否有类似的函数?

 

解决方案:


如何用SQL Server Function实现Join


-- 使用用户定义函数,配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT ‘a‘,1
UNION ALL SELECT ‘a‘,2
UNION ALL SELECT ‘b‘,1
UNION ALL SELECT ‘b‘,2
UNION ALL SELECT ‘b‘,3
GO
--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=‘‘
SELECT @re=@re+‘,‘+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,‘‘))
END
GO
--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
GO

 

如何用SQL CLR实现Join


步骤一:

 

开始,运行Visual Studio 2012,选择“New Project”,选择“Visual C#”,“类库”,命名类库为fnConcatenate。

 

技术分享

 

步骤二:

 

默认,Visual studio创建一个空的类命名为“Class1.cs”,右键重命名为Concatenate.cs。

 

技术分享

 

步骤三:

 

双击“Concatenate.cs”文件,输入如下代码:

 

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 result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = -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 any
if (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());
}
}

 

步骤四:

 

从BUILD菜单,选择“Build fnConcatenate”。编译后,在bin目录生成“fnConcatenate.dll”文件。拷贝该文件到SQL Server可访问目录,如D:\MSSQL\DATA\CLRLibraries。

 

技术分享

 

技术分享

 

步骤五:

 

打开SQL Server Management Studio,连接到需要部署该DLL的实例。

 

技术分享

 

步骤六:

 

CLR集成默认在SQL Server是禁用的。执行下面的命令启用CRL集成。

 

sp_configure ‘show advanced options‘, 1
RECONFIGURE
GO
sp_configure ‘clr enabled‘, 1
RECONFIGURE
GO
sp_configure ‘show advanced options‘, 0
RECONFIGURE
GO

 

步骤七:

 

在应用的数据库中通过该DLL创建Assemblies。

 

USE AdvantureWorks2012
GO
create assembly fnConcatenate
from N‘D:\MSSQL\DATA\CLRLibraries\fnConcatenate.dll‘
with permission_set = safe
go

 

技术分享

 

步骤八:

 

创建concatenate函数,语法类似创建标准函数,除了使用“External”定位实际的程序逻辑到你的DLL中。

create aggregate concatenate (
@value nvarchar(max),
@separator nvarchar(10)
) returns nvarchar(max)
external name fnConcatenate.Concatenate
go

 

技术分享

 

步骤九:

 

测试concatenate函数

 

select dbo.concatenate(FirstName, ‘,‘) from Person.Person

 

技术分享



本文出自 “SQL Server Deep Dives” 博客,请务必保留此出处http://ultrasql.blog.51cto.com/9591438/1593982

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。