如何从SQL Server存储的SQL语句字符串中提取参数？

SQL Server 实现方案 #

方法1：自定义函数提取参数 #

先创建一个标量值函数，用于从SQL语句中提取符合规则的@参数（@后至少跟一个字母，以非字母字符结尾），同时自动去重：

CREATE FUNCTION dbo.ExtractSQLParameters(@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX) = '';
    DECLARE @pos INT = PATINDEX('%@[A-Za-z]%', @input);
    
    WHILE @pos > 0
    BEGIN
        DECLARE @paramStart INT = @pos;
        -- 定位参数结束位置：第一个非字母字符
        DECLARE @paramEnd INT = PATINDEX('%[^A-Za-z]%', SUBSTRING(@input, @pos + 1, LEN(@input) - @pos + 1)) + @pos;
        
        -- 处理参数在字符串末尾的情况
        IF @paramEnd = @pos
            SET @paramEnd = LEN(@input) + 1;
            
        DECLARE @param NVARCHAR(MAX) = SUBSTRING(@input, @paramStart, @paramEnd - @paramStart);
        
        -- 避免重复添加参数
        IF CHARINDEX(@param + ',', @result + ',') = 0
            SET @result = CASE WHEN @result = '' THEN @param ELSE @result + ', ' + @param END;
            
        -- 截断字符串，继续查找下一个参数
        SET @input = SUBSTRING(@input, @paramEnd, LEN(@input) - @paramEnd + 1);
        SET @pos = PATINDEX('%@[A-Za-z]%', @input);
    END
    
    RETURN CASE WHEN @result = '' THEN NULL ELSE @result END;
END

调用函数查询目标表：

SELECT 
    ID,
    dbo.ExtractSQLParameters(Query) AS Parameters
FROM YourTableName;

方法2：递归CTE实现（无需自定义函数） #

通过递归CTE遍历字符串提取参数，最后分组合并结果：

WITH ParamCTE AS (
    SELECT 
        ID,
        Query,
        PATINDEX('%@[A-Za-z]%', Query) AS Pos,
        CAST('' AS NVARCHAR(MAX)) AS Params
    FROM YourTableName
    
    UNION ALL
    
    SELECT 
        ID,
        SUBSTRING(Query, Pos + PATINDEX('%[^A-Za-z]%', SUBSTRING(Query, Pos + 1, LEN(Query) - Pos + 1)) + Pos, LEN(Query)),
        PATINDEX('%@[A-Za-z]%', SUBSTRING(Query, Pos + PATINDEX('%[^A-Za-z]%', SUBSTRING(Query, Pos + 1, LEN(Query) - Pos + 1)) + Pos, LEN(Query))),
        CASE 
            WHEN Params = '' THEN SUBSTRING(Query, Pos, PATINDEX('%[^A-Za-z]%', SUBSTRING(Query, Pos + 1, LEN(Query) - Pos + 1)))
            ELSE Params + ', ' + SUBSTRING(Query, Pos, PATINDEX('%[^A-Za-z]%', SUBSTRING(Query, Pos + 1, LEN(Query) - Pos + 1)))
        END
    FROM ParamCTE
    WHERE Pos > 0
)
SELECT 
    ID,
    CASE WHEN MAX(Params) = '' THEN NULL ELSE MAX(Params) END AS Parameters
FROM ParamCTE
GROUP BY ID
ORDER BY ID;

Python 实现方案 #

利用正则表达式匹配符合规则的参数，自动过滤'@'这类无效格式：

import re
import pandas as pd

# 模拟数据表
data = {
    'ID': [1, 2, 3],
    'Query': [
        'SELECT * FROM table WHERE id = @organisationId',
        'SELECT * FROM topic WHERE creation_time <=@startDate AND creation_time >= @endDate AND id = @enterOrgHere',
        'SELECT name + \'@\' + domain FROM user'
    ]
}
df = pd.DataFrame(data)

# 正则规则：@开头，后跟至少一个字母
pattern = r'@[A-Za-z]+'

def extract_params(query):
    params = re.findall(pattern, query)
    # 去重并保持顺序
    unique_params = list(dict.fromkeys(params))
    return ', '.join(unique_params) if unique_params else None

df['Parameters'] = df['Query'].apply(extract_params)
print(df[['ID', 'Parameters']])

Excel 实现方案 #

通过VBA宏实现参数提取：

1. 按Alt+F11打开VBA编辑器，插入新模块
2. 粘贴以下代码：

Function ExtractParameters(inputStr As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Global = True
    regex.Pattern = "@[A-Za-z]+"
    
    Dim matches As Object
    Set matches = regex.Execute(inputStr)
    
    Dim result As String
    result = ""
    Dim match As Object
    For Each match In matches
        ' 去重处理
        If InStr(result, match.Value & ", ") = 0 Then
            result = IIf(result = "", match.Value, result & ", " & match.Value)
        End If
    Next
    
    ExtractParameters = IIf(result = "", "NULL", result)
End Function

3. 在Excel参数列输入公式=ExtractParameters(B2)（假设B列为Query列），下拉填充即可。

C# 实现方案 #

使用System.Text.RegularExpressions处理参数提取：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

class Program
{
    static void Main()
    {
        // 模拟数据
        var data = new List<(int ID, string Query)>
        {
            (1, "SELECT * FROM table WHERE id = @organisationId"),
            (2, "SELECT * FROM topic WHERE creation_time <=@startDate AND creation_time >= @endDate AND id = @enterOrgHere"),
            (3, "SELECT name + '@' + domain FROM user")
        };
        
        foreach (var item in data)
        {
            var parameters = ExtractParameters(item.Query);
            Console.WriteLine($"ID: {item.ID}, Parameters: {parameters ?? "NULL"}");
        }
    }
    
    static string ExtractParameters(string query)
    {
        var pattern = @"@[A-Za-z]+";
        var matches = Regex.Matches(query, pattern);
        
        // 去重后拼接为逗号分隔字符串
        var uniqueParams = matches.Cast<Match>()
                                  .Select(m => m.Value)
                                  .Distinct()
                                  .ToList();
        
        return uniqueParams.Any() ? string.Join(", ", uniqueParams) : null;
    }
}

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