如何在 CLR UDF 中返回 nvarchar(max)？

发布于 2024-07-10 06:17:21 字数 676 浏览 8 评论 0原文

假设以下定义：

/// <summary>
/// Replaces each occurrence of sPattern in sInput with sReplace. This is done 
/// with the CLR: 
/// new RegEx(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace). 
/// The result of the replacement is the return value.
/// </summary>
[SqlFunction(IsDeterministic = true)]
public static  SqlString FRegexReplace(string sInput, string sPattern, 
      string sReplace)
{
    return new Regex(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace);
}

为 sInput 传递一个长度 > nvarchar(max) 值 4000 将导致值被截断（即调用此 UDF 的结果是 nvarchar(4000)，而不是 nvarchar(max)。

原文

Assuming following definition:

/// <summary>
/// Replaces each occurrence of sPattern in sInput with sReplace. This is done 
/// with the CLR: 
/// new RegEx(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace). 
/// The result of the replacement is the return value.
/// </summary>
[SqlFunction(IsDeterministic = true)]
public static  SqlString FRegexReplace(string sInput, string sPattern, 
      string sReplace)
{
    return new Regex(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace);
}

Passing in a nvarchar(max) value for sInput with a length > 4000 will result in the value being truncated (i.e. the result of calling this UDF is nvarchar(4000) as opposed to nvarchar(max).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

葬花如无物 2024-07-17 06:17:21

哦，无论如何，我自己找到了答案：

/// <summary>
/// Replaces each occurrence of sPattern in sInput with sReplace. This is done 
/// with the CLR: 
/// new RegEx(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace). 
/// The result of the replacement is the return value.
/// </summary>
[SqlFunction(IsDeterministic = true)]
[return: SqlFacet(MaxSize = -1)]
public static  SqlString FRegexReplace([SqlFacet(MaxSize = -1)]string sInput, 
       string sPattern, string sReplace)
{
    return new Regex(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace);
}

这个想法是暗示 SQL Server 输入和返回值不是默认的 nvarchar(4000)，而是具有不同的大小。

我学到了一个关于属性的新技巧：它们可以添加到参数以及方法本身（非常明显），但也使用 [return: AttributeName(Parameter) 添加到返回值=值, ...)] 语法。

Oh, whatever, I found the answer myself:

/// <summary>
/// Replaces each occurrence of sPattern in sInput with sReplace. This is done 
/// with the CLR: 
/// new RegEx(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace). 
/// The result of the replacement is the return value.
/// </summary>
[SqlFunction(IsDeterministic = true)]
[return: SqlFacet(MaxSize = -1)]
public static  SqlString FRegexReplace([SqlFacet(MaxSize = -1)]string sInput, 
       string sPattern, string sReplace)
{
    return new Regex(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace);
}

The idea is to hint to SQL Server that the input and return values are not the default nvarchar(4000), but have a different size.

I learned a new trick regarding attributes: They can be added to the parameters as well as the method itself (quite obvious), but also to the return value with the [return: AttributeName(Parameter=Value, ...)] Syntax.

回复收藏 0 原文