SQL Server 正則替換函數 regexReplace 實現與應用

SQL Server 原生不支援正則表達式替換，但可透過 OLE 自動化調用 VBScript.RegExp COM 組件實現。本文提供生產可用的標量函數 dbo.regexReplace。

函數定義

CREATE FUNCTION dbo.regexReplace (
    @source    NVARCHAR(MAX),   -- 源字符串
    @regexp    VARCHAR(1000),   -- 正則模式
    @replace   VARCHAR(1000),   -- 替換字符串
    @globalReplace BIT = 1,     -- 全局替換
    @ignoreCase  BIT = 0        -- 忽略大小寫
)
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @hr INT, @objRegExp INT, @result VARCHAR(5000);
    EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT;
    IF @hr <> 0 BEGIN EXEC sp_OADestroy @objRegExp; RETURN NULL; END
    EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp;
    IF @hr <> 0 BEGIN EXEC sp_OADestroy @objRegExp; RETURN NULL; END
    EXEC @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace;
    IF @hr <> 0 BEGIN EXEC sp_OADestroy @objRegExp; RETURN NULL; END
    EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase;
    IF @hr <> 0 BEGIN EXEC sp_OADestroy @objRegExp; RETURN NULL; END
    EXEC @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace;
    IF @hr <> 0 BEGIN EXEC sp_OADestroy @objRegExp; RETURN NULL; END
    EXEC sp_OADestroy @objRegExp;
    RETURN @result;
END

啟用前置條件

SSMS →「外圍應用配置器」→「功能的外圍應用配置」→「Ole 自動化程序」→ 勾選「啟用 Ole 自動化」。

應用示例

性能與限制提示

• COM 互操作開銷較大，**不建議在大結果集逐行調用**；適用於 ETL 清洗、報表生成等低頻場景。
• 返回類型限制 VARCHAR(1000)，長文本需改用 NVARCHAR(MAX) 並分段處理。
• SQL Server 2016+ 可考慮 TRANSLATE、STRING_ESCAPE 或 CLR 正則替代方案。