Russ Suter
asked on
Having trouble adding a .NET CLR to SQL Server
Here is my C# class
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
namespace ASI.Utilities
{
public class SqlRegex
{
[SqlFunction(Name = "Replace", IsDeterministic = true)]
public static string Replace(SqlString input, SqlString pattern, SqlString replacement)
{
if (input.IsNull || pattern.IsNull || replacement.IsNull)
{
return null;
}
return Regex.Replace(input.ToString(), pattern.ToString(), replacement.ToString());
}
}
}
and here is my SQLsp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
GO
CREATE SCHEMA [Regex] AUTHORIZATION [CON5USERROLE]
GO
IF EXISTS (SELECT 1 FROM sys.assemblies WHERE [name] = N'RegexFunctions')
DROP ASSEMBLY [RegexFunctions]
GO
CREATE ASSEMBLY [RegexFunctions] FROM 'C:\Temp\T-SQL Regex.dll' WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION dbo.RegexReplace
(
@input VARCHAR(4000),
@pattern VARCHAR(4000),
@replacement VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS EXTERNAL NAME
RegexFunctions.[ASI.Utilities.SqlRegex].Replace
GO
The error message I'm getting saysCREATE FUNCTION for "RegexReplace" failed because T-SQL and CLR types for return value do not match.Where have I gone wrong?
ASKER
Better than what? I want to return a single VARCHAR(4000), not a table. This is to be a scalar valued function.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, good to know, thanks. I guess NVARCHAR it must be. I just can't help but wonder if I couldn't get better performance by using VARCHAR but I guess I'll never know.
check this example
CLR Assembly RegEx Functions for SQL Server by Example
https://www.red-gate.com/simple-talk/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/