将字符串分割为表表经常用到，这里 SQL表值函数与CLR 表值函数，两种实现方法例出来如下：
SELECT * FROM FP_EMSDB_PUB.dbo.SqlSplit('/','1oz/1.5oz/2oz/3oz')
效果如下：
SQL实现表值函数
CREATE FUNCTION [dbo].[splitStr](
@s varchar(max), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(id int IDENTITY(1,1), col varchar(max)) --建立临时表保存分割后的字符
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
创建CLR 表值函数
--注册表值函数 SqlSplit
CREATE FUNCTION[dbo].[SqlSplit] (@separator NVARCHAR(32),@string NVARCHAR(MAX))
RETURNS TABLE
(
SerialNumber INT,
StringValue NVARCHAR(1024)
)
WITH EXECUTE AS CALLER --用于在用户在执行函数的时候对引用的对象进行权限检查
AS
EXTERNAL NAME[SQLfunctionAssembly].[SQLClr.SQLfunction].[SqlSplit] --EXTERNAL NAME 程序集名.类名.方法名
GO
CLR实现表值函数
public partial class SQLfunction
{
/// <summary>
/// SQL Server 字符串分割方法
/// </summary>
/// <param name="separator"></param>
/// <param name="pendingString"></param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.Read,
IsDeterministic = true,
Name = "SqlSplit",
FillRowMethodName = "SqlSplit_FillRow",
TableDefinition = "SerialNumber int,StringValue nvarchar(1024)")]
public static IEnumerable SqlSplit(SqlString separator, SqlString pendingString)
{
string _separator = string.Empty;
string _pendingString = string.Empty;
if (pendingString.IsNull) return null;
_pendingString = pendingString.ToString();
if (string.IsNullOrEmpty(_pendingString)) return null;
_separator = separator.IsNull ? "," : separator.ToString();
_separator = string.IsNullOrEmpty(_separator) ? "," : _separator;
string[] strs = _pendingString.Split(new string[] { _separator }, StringSplitOptions.RemoveEmptyEntries);
List<ResultData> resultDataList = new List<ResultData>();
for (int i = ; i < strs.Length; i++)
{
resultDataList.Add(new ResultData(i + , strs[i]));
}
return resultDataList;
} /// <summary>
/// 填充数据方法
/// </summary>
/// <param name="obj"></param>
/// <param name="serialNumber"></param>
/// <param name="stringValue"></param>
public static void SqlSplit_FillRow(Object obj, out SqlInt32 SerialNumber, out SqlString StringValue)
{
ResultData resultData = (ResultData)obj;
SerialNumber = resultData.SerialNumber;
StringValue = resultData.StringValue;
} /// <summary>
/// 定义返回类型
/// </summary>
public class ResultData
{
/// <summary>
/// 序号，即行号
/// </summary>
public SqlInt32 SerialNumber { get; set; } /// <summary>
/// 分割后的每个子字符串
/// </summary>
public SqlString StringValue { get; set; } public ResultData(SqlInt32 serialNumber, SqlString stringValue)
{
SerialNumber = serialNumber;
StringValue = stringValue;
}
}
}