microsoft sql equivalent of mysql REGEXP

Question

I am trying to reconstruct a database query I created for mysql in Microsoft sql. I am looking for a term in Microsoft sql, which acts like REGEXP

here is an example of how am using the term

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

any help would be much appreciated

Answer 1

The only way you can do this in SQL Server (2005 and up only) is to use CLR functions; regular expressions as part of native SQL queries isn't standard.

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

Answer 2

Here you go (compile as 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;
  }
}