如何将PostgreSQL正则匹配脚本转换为SQL Server 2016兼容版本？

没问题，我来帮你把PostgreSQL的正则匹配脚本转换成SQL Server 2016兼容的版本～

核心问题说明 #

PostgreSQL里的~操作符用于POSIX正则匹配，但SQL Server 2016原生没有直接对应的语法。我们可以根据正则的复杂程度，选择两种方案：使用内置的PATINDEX函数（适合简单正则），或者创建CLR自定义函数（支持复杂POSIX正则）。

方案1：使用PATINDEX处理简单正则 #

PATINDEX是SQL Server内置的模式匹配函数，支持T-SQL通配符语法，大部分简单的POSIX正则可以转换为对应的T-SQL模式。

示例1：匹配以SAG开头的注册码 #

PostgreSQL原脚本：

SELECT matricule FROM student WHERE matricule ~ '^SAG*'

转换为SQL Server 2016版本（注：原正则^SAG*若意图是匹配以SAG开头的字符串，调整为T-SQL模式后）：

SELECT matricule 
FROM student 
WHERE PATINDEX('^SAG%', matricule) = 1

• 解释：^在T-SQL中表示字符串开头，%匹配任意长度的任意字符（等价于POSIX的.*），PATINDEX返回匹配模式的起始位置，返回1表示从开头就匹配。

示例2：匹配以SAG开头且后跟100或101的注册码 #

PostgreSQL原脚本：

SELECT matricule FROM student WHERE matricule ~ 'SAG(100|101)()*'

由于PATINDEX不支持|分支语法，我们需要拆分条件为多个OR判断：

SELECT matricule 
FROM student 
WHERE PATINDEX('^SAG100%', matricule) = 1 
   OR PATINDEX('^SAG101%', matricule) = 1

方案2：CLR自定义函数支持复杂POSIX正则 #

如果你的正则包含大量POSIX特性（比如分支、捕获组、特殊元字符），PATINDEX无法满足需求，可以通过SQL Server的CLR集成创建自定义正则匹配函数，完全支持POSIX正则语法。

步骤1：启用CLR集成 #

首先需要在SQL Server中开启CLR支持（需要管理员权限）：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

步骤2：创建C#正则函数 #

编写一个简单的C#类库，实现正则匹配逻辑：

using System;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public class RegexTools
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean RegexMatch(SqlString input, SqlString pattern)
    {
        if (input.IsNull || pattern.IsNull)
            return SqlBoolean.False;
        
        // 这里可以根据需求添加RegexOptions，比如IgnoreCase、Multiline等
        return Regex.IsMatch(input.Value, pattern.Value);
    }
}

将代码编译为DLL文件（比如RegexTools.dll）。

步骤3：部署到SQL Server #

在SQL Server中创建程序集和自定义函数：

-- 创建程序集
CREATE ASSEMBLY RegexTools 
FROM 'C:\Your\Path\To\RegexTools.dll'
WITH PERMISSION_SET = SAFE;

-- 创建自定义函数
CREATE FUNCTION dbo.RegexMatch(@input NVARCHAR(MAX), @pattern NVARCHAR(MAX))
RETURNS BIT
AS EXTERNAL NAME RegexTools.RegexTools.RegexMatch;

步骤4：使用自定义函数 #

现在就可以像PostgreSQL那样使用动态正则匹配了：

DECLARE @regex NVARCHAR(100) = 'SAG(100|101)()*'; -- 动态POSIX正则
SELECT matricule 
FROM student 
WHERE dbo.RegexMatch(matricule, @regex) = 1;

内容的提问来源于stack exchange，提问作者Maha makni