原帖地址：http://bbs.51cto.com/thread-1133863-1.html
问题描述：
VB 中有两个非常好用的字符串处理函数：
Split（字符串，分隔符）作用：将【字符串】以【分隔符】作为边界，分解成数组。 返回：一个字符串数组。
Join（字符数组，分隔符）作用：将【字符数组】中的元素，以【分隔符】作为边界，连接成一个字符串。返回：一个字符串。
请教老师们，SQL里是否有类似的函数？
解决方案：
如何用SQL Server Function实现Split？
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
--Split 表函数将一个字符串按指定分隔符进行分割，返回一个表。
create
function
split(
@string
varchar
(255),
--待分割字符串
@separator
varchar
(255)
--分割符
)
returns
@array
table
(item
varchar
(255))
as
begin
declare
@
begin
int
,@
end
int
,@item
varchar
(255)
set
@
begin
= 1
set
@
end
=charindex(@separator,@string,@
begin
)
while(@
end
<>0)
begin
set
@item =
substring
(@string,@
begin
,@
end
-@
begin
)
insert
into
@array(item)
values
(@item)
set
@
begin
= @
end
+1
set
@
end
=charindex(@separator,@string,@
begin
)
end
set
@item =
substring
(@string,@
begin
,len(@string)+1-@
begin
)
if (len(@item)>0)
insert
into
@array(item)
values
(
substring
(@string,@
begin
,len(@string)+1-@
begin
))
return
end
|
如何用SQL CLR实现Split？
步骤一：
开始，运行Visual Studio 2012，选择“New Project”，选择“Visual C#”，“类库”，命名类库为fnSplit。
步骤二：
默认，Visual studio创建一个空的类命名为“Class1.cs”，右键重命名为CLRFunction.cs。
步骤三：
双击“CLRFunction.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
|
using
System;
using
System.Collections;
using
System.Text;
using
Microsoft.SqlServer.Server;
using
System.Data.SqlTypes;
namespace
fnSplit
{
public
static
class
CLRFunctions
{
//SQL Functions require an additional "SqlFunction" Attribute.
//This attribute provides SQL server with additional meta data information it needs
//regarding our custom function. In this example we are not accessing any data, and our
//function is deterministic. So we let SQL know those facts about our function with
//the DataAccess and IsDeterministic parameters of our attribute.
//Additionally, SQL needs to know the name of a function it can defer to when it needs
//to convert the object we have returned from our function into a structure that SQL
//can understand. This is provided by the "FillRowMethodName" shown below.
[SqlFunction(
DataAccess = DataAccessKind.None,
FillRowMethodName =
"MyFillRowMethod"
,IsDeterministic=
true
)
]
//SQL Functions must be declared as Static. Table Valued functions must also
//return a class that implements the IEnumerable interface. Most built in
//.NET collections and arrays already implement this interface.
public
static
IEnumerable Split(
string
stringToSplit,
string
delimiters)
{
//One line of C# code splits our string on one or more delimiters...
//A string array is one of many objects that are returnable from
//a SQL CLR function - as it implements the required IEnumerable interface.
string
[] elements = stringToSplit.Split(delimiters.ToCharArray());
return
elements;
}
//SQL needs to defer to user code to translate the an IEnumerable item into something
//SQL Server can understand. In this case we convert our string to a SqlChar object...
public
static
void
MyFillRowMethod(Object theItem,
out
SqlChars results)
{
results =
new
SqlChars(theItem.ToString());
}
}
}
|
步骤四：
从BUILD菜单，选择“Build fnSplit”。编译后，在bin目录生成“fnSplit.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
|
Create
Assembly fnSplit
from
'D:\MSSQL\DATA\CLRLibraries\fnSplit.dll'
with
Permission_set = SAFE
GO
|
步骤八：
创建Split函数，语法类似创建标准函数，除了使用“External”定位实际的程序逻辑到你的DLL中。
|
1
2
3
4
5
6
7
|
Create
Function
fnSplit(@StringToSplit nvarchar(
max
), @splitOnChars nvarchar(
max
) )
returns
Table
(
Results nvarchar(
max
)
)
AS
External
name
fnSplit.[fnSplit.CLRFunctions].Split;
GO
|
步骤九：
测试Split函数
|
1
|
SELECT
*
FROM
dbo.fnSplit(
'1,2,3,4,5:6:7~8~9'
,
',:~'
)
|
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1593531 ，如需转载请自行联系原作者
