You need to enable JavaScript to run this app. Python正则提取混合SQL字符串中方括号内的~分隔结构化列表
优惠活动
大模型
产品
解决方案
定价
更多

Python正则提取混合SQL字符串中方括号内的~分隔结构化列表

核心实现方案

不需要针对复杂的SQL外层结构写匹配逻辑,分两步处理即可:

  • 优先匹配所有被非嵌套方括号包裹的内容,完全忽略外层的SQL表达式、括号、字符串等无关内容
  • 对提取到的每个方括号内的字符串,按分隔符~切分为层级列表

对应的核心匹配正则非常简单:\[([^\[\]]+)\]

  • \[/\] 匹配成对的方括号边界
  • ([^\[\]]+) 捕获组提取方括号内部所有不包含方括号的连续内容,天然适配任意长度的层级结构,不需要写死层级数量

完整可运行Python代码

import re

# 待处理字符串列表
test_strings = [
    '[Business Layer~Project Owning Org~Proj Owning Dept ID]',
    '[Business Layer~Project Owning Org~Proj Owning Org Name]',
    '[Business Layer~Project~Proj No]',
    '[Business Layer~Project~Proj Name]',
    "([Business Layer~Project~Proj No]) || COALESCE((' - ' || ([Business Layer~Project~Proj Name])), ' - ')",
    '[Project Assignment Fact~Task~Task No]',
    '[Project Assignment Fact~Task~Task Name]',
    "([Project Assignment Fact~Task~Task No]) || COALESCE((' - ' || ([Project Assignment Fact~Task~Task Name])), ' - ')",
    "([Business Layer~Project~Proj No]) || COALESCE((' - ' || ([Project Assignment Fact~Task~Task No])), ' - ') || COALESCE((' - ' || ([Project Assignment Fact~Task~Task Name])), ' - ')",
    '[Business Layer~Project Cost~Short Code Alias]',
    '[Business Layer~Expenditure Type~Expenditure Category Name]',
    '[Business Layer~Expenditure Type~Expenditure Type Parent Code]',
    '[Business Layer~Expenditure Type~Expend Type Desc]',
    '[Business Layer~Expenditure Owning Org~Exp Owning Org Name]',
    '[Business Layer~Transaction Source~Trans Source]',
    '[Business Layer~Employee~Employee Name]',
    '[Business Layer~Project Cost~Expend Comment]',
    '[Business Layer~Project Cost~PO No]',
    '[Business Layer~Project Cost~PV Invoice No]',
    '[Business Layer~Vendor~Vendor Name]',
    '[Business Layer~Scenario~Scenario Name]',
    '[Business Layer~ERS Employee~ERS Employee Name]',
    '[Business Layer~ERS Employee~ERS Employee Number]',
    '[Business Layer~Project Cost~Vehicle Tag No]',
    '[Business Layer~Project Cost~Vehicle Make]',
    '[Business Layer~Project Cost~Vehicle Model]',
    '[Business Layer~Project Cost~Vehicle Mileage]',
    '[Business Layer~Project Type~Proj Type Code]',
    '[Business Layer~GL Period~GL Period Start Date]',
    '[Business Layer~Project Cost~Burdened Cost Amt]'
]

def extract_field_paths(s: str) -> list[list[str]]:
    # 提取所有方括号内的内容
    raw_matches = re.findall(r'\[([^\[\]]+)\]', s)
    # 按~切分每个路径,生成嵌套列表
    return [match.split('~') for match in raw_matches]

# 批量处理测试
for idx, s in enumerate(test_strings):
    result = extract_field_paths(s)
    print(f"第{idx+1}条处理结果:{result}")

效果验证

针对复杂SQL串:

"([Business Layer~Project~Proj No]) || COALESCE((' - ' || ([Business Layer~Project~Proj Name])), ' - ')"

处理后输出完全匹配预期:

[['Business Layer', 'Project', 'Proj No'], ['Business Layer', 'Project', 'Proj Name']]

过往尝试失效的原因

  • 写死3个层级的正则,仅能匹配固定长度的路径结构,遇到任意层级长度的字段就会失效,同时无法匹配分布在SQL不同位置的多个独立方括号
  • 带^\[的开头锚定逻辑,只能匹配字符串最开头的方括号,无法捕获SQL中间嵌套的方括号内容
  • 包含[^(~\)]的排除逻辑,把~作为非目标字符排除,自然无法识别方括号内部的层级分隔符

注:如果后续出现方括号内部嵌套转义的方括号(如[Layer~Field\[]),可以调整正则为支持转义的版本:\[((?:[^\[\]\\]|\\.)+)\],当前业务场景下不需要额外适配。


内容的提问来源于stack exchange,提问作者Sam Dean

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.30 09:15:00