【sqlserver】【自定义函数】正则匹配实现_sql server 正则自定义函数-优快云博客

本文链接：https://blog.youkuaiyun.com/AskTommorow/article/details/53896005

本文介绍了一个SQL Server中自定义的正则表达式匹配函数RegexMatch的实现方法。该函数通过使用VBScript.RegExp对象来判断输入字符串是否符合指定的正则表达式模式，并返回布尔值。文章还提供了如何调用此函数的具体示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

自定义函数实现

CREATE FUNCTION [dbo].[RegexMatch]
    (
      @pattern VARCHAR(2000),
      @matchstring VARCHAR(8000)--Varchar(8000) got SQL Server 2000
    )
RETURNS INT
/* The RegexMatch returns True or False, indicating if the regular expression matches (part of) the string. (It returns null if there is an error).
When using this for validating user input, you'll normally want to check if the entire string matches the regular expression. To do so, put a caret at the start of the regex, and a dollar at the end, to anchor the regex at the start and end of the subject string.
*/ 
AS BEGIN
    DECLARE @objRegexExp INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(255),
        @hr INT,
        @match BIT

    SELECT  @strErrorMessage = 'creating a regex object'
    EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
        --Specifying a case-insensitive match 
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
        --Doing a Test' 
    IF @hr = 0 
        EXEC @hr= sp_OAMethod @objRegexExp, 'Test', @match OUT, @matchstring
    IF @hr <> 0 
        BEGIN
            RETURN NULL
        END
    EXEC sp_OADestroy @objRegexExp
    RETURN @match
   END



GO

调用DEMO

select dbo.RegexMatch('^dy[0-9]{3,}','dy123') -- dy开头，之后三位是数字的结果为1，否则为0

参考：http://blog.youkuaiyun.com/weiwenhp/article/details/6943834