I have a very simple CLR Function for doing Regex Matching
public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull)
return SqlBoolean.False;
return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
}
It allows me to write a SQL Statement Like.
SELECT * FROM dbo.table1 WHERE dbo.RegexMatch(column1, '[0-9][A-Z]') = 1
-- match entries in col1 like 1A, 2B etc...
I'm just thinking it would be nice to reformulate that query so it could be called like
SELECT * FROM dbo.table1 WHERE column1 REGEXLIKE '[0-9][A-Z]'
Is it possible to create new comparison operators using CLR Code. (I'm guessing from my brief glance around the web that the answer is NO, but no harm asking)
For SQL Server users and application developers, CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table valued), and user-defined aggregate functions using any . NET Framework language, including Microsoft Visual Basic .
SQL CLR or SQLCLR (SQL Common Language Runtime) is technology for hosting of the Microsoft . NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment.
To determine if CLR is enabled, execute the following commands: EXEC SP_CONFIGURE 'show advanced options', '1'; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'clr enabled';
No you cannot. You can create functions, stored procedures, triggers and so forth - but there's no provision to create new T-SQL operators or commands. Not in SQL Server 2008R2 either, as far as I can tell.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With