原帖地址：http://bbs.51cto.com/thread-1133863-1.html
问题描述：
VB 中有两个非常好用的字符串处理函数：
Split（字符串，分隔符）作用：将【字符串】以【分隔符】作为边界，分解成数组。 返回：一个字符串数组。
Join（字符数组，分隔符）作用：将【字符数组】中的元素，以【分隔符】作为边界，连接成一个字符串。返回：一个字符串。
请教老师们，SQL里是否有类似的函数？
解决方案：
如何用SQL Server Function实现Join？
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
-- 使用用户定义函数，配合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”文件，输入如下代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
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集成。
|
1
2
3
4
5
6
7
8
9
|
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。
|
1
2
3
4
5
6
|
USE AdvantureWorks2012
GO
create
assembly fnConcatenate
from
N
'D:\MSSQL\DATA\CLRLibraries\fnConcatenate.dll'
with
permission_set = safe
go
|
步骤八：
创建concatenate函数，语法类似创建标准函数，除了使用“External”定位实际的程序逻辑到你的DLL中。
|
1
2
3
4
5
6
|
create
aggregate concatenate (
@value nvarchar(
max
),
@separator nvarchar(10)
)
returns
nvarchar(
max
)
external
name
fnConcatenate.Concatenate
go
|
步骤九：
测试concatenate函数
|
1
|
select
dbo.concatenate(FirstName,
','
)
from
Person.Person
|
本文转自UltraSQL51CTO博客，原文链接： http://blog.51cto.com/ultrasql/1593982，如需转载请自行联系原作者
