SQL Regex function that is similar to the MySql REGEX Function

Question

I am looking for a function that would be able to do the same thing as the MySQL REGEX function for TSQL. Basically i need my Query to look something like the following:

SELECT * FROM Routing WHERE (@Message REGEX RouteRegex);

I am not to keen to use CLR at this point in time.

Any Ideas?

Answer 1

This link is an article to writing Regular Expressions via VBScript.Regex library on your server: http://www.sqlteam.com/article/regular-expressions-in-t-sql

It communicates with the Regex library via OLE Automation which is turned off by default since it is a security risk.

My opinion is go with Regex via the CLR.

Answer 2

LFSR is right about the CLR. Go for CLR but the function below also works great. I was testing the code while LFSR post the answer above.

CREATE FUNCTION dbo.find_regular_expression
(
 @source varchar(5000),
 @regexp varchar(1000),
 @ignorecase bit = 0
)
RETURNS bit
AS
BEGIN
 DECLARE @hr integer
 DECLARE @objRegExp integer
 DECLARE @objMatches integer
 DECLARE @objMatch integer
 DECLARE @count integer
 DECLARE @results bit

 EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
 IF @hr <> 0 BEGIN
  SET @results = 0
  RETURN @results
 END
 EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
 IF @hr <> 0 BEGIN
  SET @results = 0
  RETURN @results
 END
 EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
 IF @hr <> 0 BEGIN
  SET @results = 0
  RETURN @results
 END
 EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
 IF @hr <> 0 BEGIN
  SET @results = 0
  RETURN @results
 END 
 EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
 IF @hr <> 0 BEGIN
  SET @results = 0
  RETURN @results
 END
 EXEC @hr = sp_OADestroy @objRegExp
 IF @hr <> 0 BEGIN
  SET @results = 0
  RETURN @results
 END
RETURN @results
END

Answer 3

I think PATINDEX is the closest you'll get for native t-sql.

http://msdn.microsoft.com/en-us/library/ms188395.aspx

also see

http://msdn.microsoft.com/en-us/library/ms187489.aspx

Answer 4

It is a bit like using a hammer to make buttered toast, but you can use the regex support in the xml datatype via the pattern facet. Then you determine if the regex matches or not by trying to parse as typed xml, if you jump to the catch block with err 6926 then your regex didn't match (simple type validation err). Here is an example that enforces the regex of 3 decimal digits followed by 6 letters:

create xml schema collection regexTest1 as '<xs:schema targetNamespace="http://example/regexTest"
                  elementFormDefault="qualified"
                  xmlns="http://example/regexTest"
                  xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
                  xmlns:xs="http://www.w3.org/2001/XMLSchema"&gt;
    <xs:element name="regexTest">
     <xs:simpleType>
      <xs:restriction base="xs:string">
       <xs:pattern value="\p{Nd}{3}\p{L}{6}"></xs:pattern>
      </xs:restriction>
     </xs:simpleType>
    </xs:element>
</xs:schema>'


declare @x xml(dbo.regexTest1), @regexPass bit
begin try
    set @x = '<regexTest xmlns="http://example/regexTest"&gt;111abcdef&lt;/regexTest&gt;'
    set @regexPass=1
end try
begin catch
    if (error_number()=6926)
    begin
     set @regexPass=0
    end
    else begin
        declare @errMsg varchar(8000), @errSev int, @errState int
        select @errMsg='Regex check was unable to process, native error: (' 
            + cast(error_number() as varchar(16))
            + ') - '
            + error_message(),
            @errSev=error_severity(),
            @errState=error_state()

        raiserror(@errMsg, @errSev, @errState)
    end
end catch
select @regexPass

Edit - if you go this route, the spec for the regex support in XSD is at http://www.w3.org/TR/xmlschema-2/#dt-regex