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
相关产品推荐
相关产品推荐

