SQL Regex function that is similar to the MySql RE

问题:

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?

回答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.

回答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

回答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

回答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">
    <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">111abcdef</regexTest>'
    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