【51CTO/BBS】请教: SQL里有没有字符串组合Join的函数??
原帖地址: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= ''
FROM tb
WHERE [email protected]
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,如需转载请自行联系原作者