How to return an nvarchar(max) in a CLR UDF?
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.
See also How to create CLR stored procedure with Nvarchar(max) parameter where you'll discover how/why you really should use the SqlChars data type. See Handling Large Object (LOB) Parameters in the CLR in MSDN.