Link to home
Start Free TrialLog in
Avatar of Russ Suter
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());
		}
	}
}

Open in new window

and here is my SQL
sp_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

Open in new window

The error message I'm getting says
CREATE FUNCTION for "RegexReplace" failed because T-SQL and CLR types for return value do not match.
Where have I gone wrong?
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

see if your  UDF  " RETURNS VARCHAR(4000)" can be adjusted to a better output  (e.g. Returns Table(SomeResult nvarchar(Max))

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/
Avatar of Russ Suter
Russ Suter

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
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.