22,210
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static string StrCount(string str)
{
// 返回字符串的长度
return str.Length.ToString();
}
};
[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillList", TableDefinition = "num int, letter nvarchar(1)")]
public static IEnumerable getLetters(string sInput)
{
List<Letters> returnTable = new List<Letters>();
int i;
for (i = 0; i <= sInput.Length - 1; i++)
{
returnTable.Add(new Letters(i+1, sInput.Substring(i,1)));
}
return returnTable;
}
public static void FillList(object Group, out SqlInt32 n, out SqlString s)
{
Letters rm = (Letters)Group;
n = rm.num;
s = rm.letter;
}
private class Letters
{
public SqlInt32 num { get; set; }
public SqlString letter { get; set; }
public Letters(SqlInt32 n, SqlString s)
{
this.num = n;
this.letter = s;
}
}
// 编译,导出DLL(略)
2.TSQL代码
-- 导入DLL(略)
-- 建函数
create function dbo.getLetters(@str nvarchar(max))
returns table(num int, letter nvarchar(1))
as
EXTERNAL NAME cxcai.UserDefinedFunctions.getLetters
-- 测试
select * from dbo.getLetters(N'Hello world')
/*
num letter
----------- ------
1 H
2 e
3 l
4 l
5 o
6
7 w
8 o
9 r
10 l
11 d
(11 row(s) affected)
*/