Microsoft SQL Server equivalent MySQL REGEXP

Question

I am trying to restore a database query created for MySQL in Microsoft SQL Server. I am looking for a SQL Server statement or function that acts like REGEXP .

Here is an example of how I use the operator:

 select * from musicdetails WHERE artistname REGEXP '^".mysql_escape_string($_GET['search'])."$' 

Answer 1

Here you go (compile as a SQL CLR assembly):

 using System.Collections; using System.Text.RegularExpressions; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [SqlFunction] public static bool RegexMatch(string expr, string regex) { return Regex.IsMatch(expr, regex); } [SqlFunction] public static string RegexReplace(string expr, string regex, string replace) { return Regex.Replace(expr, regex, replace); } [SqlFunction(FillRowMethodName="GetToken", TableDefinition="Value nvarchar(max)")] public static IEnumerable RegexSplit(string expr, string regex) { return Regex.Split(expr, regex); } public static void GetToken(object row, out string str) { str = (string) row; } } 

Answer 2

The only way to do this in SQL Server (2005 only) is to use the CLR functions; regular expressions as part of native SQL queries are not standard.

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Answer 3

Although the code in leppie's answer will compile and execute, I would not recommend it for production use. If you want RegEx features that:

• encoded using best practices (for performance and security)
• handle NULL accordingly
• work better / more efficiently
• have more options (e.g. @StartAt , @RegExOptions for IgnoreCase , MultiLine , etc.)
• provide more functionality (e.g. CaptureGroup , CaptureGroupCapture , Escape , Unescape , etc.)
• It doesn’t require additional deployment effort (i.e. one stand-alone T-SQL script that is portable and customizable and installs accurately without the need to manually enable “CLR enabled” or bind to the entered “CLR strong security” parameter) in SQL Server 2017 )

and also free, then check out the SQL # SQLCLR library (which I wrote). The free version has 13 RegEx functions (and 2 more in the full / paid version, plus the ability to increase the size of the expression cache, which can help if you often use different expressions).

I believe the RegEx_IsMatch (or RegEx_IsMatch4k ) function is what you are looking for (yes, this is in the free version).