首页 > 代码库 > 【51CTO/BBS】请教: SQL里有没有字符串分解、组合的函数??
【51CTO/BBS】请教: SQL里有没有字符串分解、组合的函数??
【51CTO/BBS】请教: SQL里有没有字符串分解、组合的函数??
原帖地址:http://bbs.51cto.com/thread-1133863-1.html
问题描述:
VB 中有两个非常好用的字符串处理函数:
Split(字符串,分隔符)作用:将【字符串】以【分隔符】作为边界,分解成数组。 返回:一个字符串数组。
Join(字符数组,分隔符)作用:将【字符数组】中的元素,以【分隔符】作为边界,连接成一个字符串。返回:一个字符串。
请教老师们,SQL里是否有类似的函数?
解决方案:
如何用SQL Server Function实现Split?
--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”文件,输入如下代码:
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集成。
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。
Create Assembly fnSplit from ‘D:\MSSQL\DATA\CLRLibraries\fnSplit.dll‘ with Permission_set = SAFE GO
步骤八:
创建Split函数,语法类似创建标准函数,除了使用“External”定位实际的程序逻辑到你的DLL中。
Create Function fnSplit(@StringToSplit nvarchar(max), @splitOnChars nvarchar(max) ) returns Table ( Results nvarchar(max) ) AS External name fnSplit.[fnSplit.CLRFunctions].Split; GO
步骤九:
测试Split函数
SELECT * FROM dbo.fnSplit(‘1,2,3,4,5:6:7~8~9‘,‘,:~‘)
本文出自 “SQL Server Deep Dives” 博客,请务必保留此出处http://ultrasql.blog.51cto.com/9591438/1593531
【51CTO/BBS】请教: SQL里有没有字符串分解、组合的函数??