如何用正则表达式拆分复杂SELECT语句的各SQL子句

数据老陈

2026-8-2

提取SELECT语句中子句的正则方案

需求说明

我在变量中存储了一个字符串形式的SELECT语句（复杂度无限制），需要从中提取出SELECT、FROM、WHERE、GROUP BY等子句，以便后续根据业务需求在子句中追加代码后重构完整SQL。

示例SQL

SELECT Accounts_Dly.Country
               AS Country,
           Accounts_Dly.LE_Book
               AS LE_Book,
           TRIM (TO_CHAR (Trans_Count_Feed_Stg.TRAN_DATE, 'dd/mm/rrrr'))
               AS TRAN_DATE,
           Accounts_Dly.Currency
               AS Currency,
           Accounts_Dly.Account_No
               AS Account_No,
           Accounts_Dly.Account_Name
               AS Account_Name,
           Accounts_Dly.Vision_GL
               AS Vision_GL,
           Accounts_Dly.Vision_OUC
               AS Vision_OUC,
           TRIM (
               TO_CHAR (Trans_Count_Feed_Stg.TRAN_AMT,
                        '999,999,999,999,999,990.00'))
               AS TRAN_AMT,
           Trans_Count_Feed_Stg.PART_TRAN_TYPE
               AS PART_TRAN_TYPE,
           Trans_Count_Feed_Stg.TRAN_PARTICULAR
               AS TRAN_PARTICULAR,
           Trans_Count_Feed_Stg.TRAN_ID
               AS TRAN_ID,
           Trans_Count_Feed_Stg.ENTRY_USER_ID
               AS ENTRY_USER_ID,
           Trans_Count_Feed_Stg.PSTD_USER_ID
               AS PSTD_USER_ID
      FROM Trans_Count_Feed_Stg  Trans_Count_Feed_Stg
           LEFT JOIN Accounts_Dly_View Accounts_Dly
               ON     trans_count_feed_stg.Country = accounts_dly.Country
                  AND trans_count_feed_stg.LE_Book = accounts_dly.LE_Book
                  AND trans_count_feed_stg.Contract_id = accounts_dly.account_no
     WHERE     Accounts_Dly.Country = 'RW'
           AND Accounts_Dly.LE_Book = '01'
           AND Trans_Count_Feed_Stg.TRAN_DATE BETWEEN '01-APR-2019'
                                                  AND '30-APR-2019'
           AND Accounts_Dly.Account_No LIKE '20__0245007001'
Group by Accounts_dly.Country;

目标拆分效果

1.SELECT Accounts_Dly.Country
               AS Country,
           Accounts_Dly.LE_Book
               AS LE_Book,
           TRIM (TO_CHAR (Trans_Count_Feed_Stg.TRAN_DATE, 'dd/mm/rrrr'))
               AS TRAN_DATE,
           Accounts_Dly.Currency
               AS Currency,
           Accounts_Dly.Account_No
               AS Account_No,
           Accounts_Dly.Account_Name
               AS Account_Name,
           Accounts_Dly.Vision_GL
               AS Vision_GL,
           Accounts_Dly.Vision_OUC
               AS Vision_OUC,
           TRIM (
               TO_CHAR (Trans_Count_Feed_Stg.TRAN_AMT,
                        '999,999,999,999,999,990.00'))
               AS TRAN_AMT,
           Trans_Count_Feed_Stg.PART_TRAN_TYPE
               AS PART_TRAN_TYPE,
           Trans_Count_Feed_Stg.TRAN_PARTICULAR
               AS TRAN_PARTICULAR,
           Trans_Count_Feed_Stg.TRAN_ID
               AS TRAN_ID,
           Trans_Count_Feed_Stg.ENTRY_USER_ID
               AS ENTRY_USER_ID,
           Trans_Count_Feed_Stg.PSTD_USER_ID
               AS PSTD_USER_ID

2.FROM Trans_Count_Feed_Stg  Trans_Count_Feed_Stg
           LEFT JOIN Accounts_Dly_View Accounts_Dly
               ON     trans_count_feed_stg.Country = accounts_dly.Country
                  AND trans_count_feed_stg.LE_Book = accounts_dly.LE_Book
                  AND trans_count_feed_stg.Contract_id = accounts_dly.account_no

3.WHERE     Accounts_Dly.Country = 'RW'
           AND Accounts_Dly.LE_Book = '01'
           AND Trans_Count_Feed_Stg.TRAN_DATE BETWEEN '01-APR-2019'
                                                  AND '30-APR-2019'
           AND Accounts_Dly.Account_No LIKE '20__0245007001'

4.Group by Accounts_dly.Country

正则实现方案

以Python为例，使用正则匹配并拆分各子句，核心是定位子句起始关键字，截取到下一个关键字或语句结束的内容。

代码实现

import re

# 代入目标SQL字符串
sql = """    SELECT Accounts_Dly.Country
               AS Country,
           Accounts_Dly.LE_Book
               AS LE_Book,
           TRIM (TO_CHAR (Trans_Count_Feed_Stg.TRAN_DATE, 'dd/mm/rrrr'))
               AS TRAN_DATE,
           Accounts_Dly.Currency
               AS Currency,
           Accounts_Dly.Account_No
               AS Account_No,
           Accounts_Dly.Account_Name
               AS Account_Name,
           Accounts_Dly.Vision_GL
               AS Vision_GL,
           Accounts_Dly.Vision_OUC
               AS Vision_OUC,
           TRIM (
               TO_CHAR (Trans_Count_Feed_Stg.TRAN_AMT,
                        '999,999,999,999,999,990.00'))
               AS TRAN_AMT,
           Trans_Count_Feed_Stg.PART_TRAN_TYPE
               AS PART_TRAN_TYPE,
           Trans_Count_Feed_Stg.TRAN_PARTICULAR
               AS TRAN_PARTICULAR,
           Trans_Count_Feed_Stg.TRAN_ID
               AS TRAN_ID,
           Trans_Count_Feed_Stg.ENTRY_USER_ID
               AS ENTRY_USER_ID,
           Trans_Count_Feed_Stg.PSTD_USER_ID
               AS PSTD_USER_ID
      FROM Trans_Count_Feed_Stg  Trans_Count_Feed_Stg
           LEFT JOIN Accounts_Dly_View Accounts_Dly
               ON     trans_count_feed_stg.Country = accounts_dly.Country
                  AND trans_count_feed_stg.LE_Book = accounts_dly.LE_Book
                  AND trans_count_feed_stg.Contract_id = accounts_dly.account_no
     WHERE     Accounts_Dly.Country = 'RW'
           AND Accounts_Dly.LE_Book = '01'
           AND Trans_Count_Feed_Stg.TRAN_DATE BETWEEN '01-APR-2019'
                                                  AND '30-APR-2019'
           AND Accounts_Dly.Account_No LIKE '20__0245007001'
Group by Accounts_dly.Country;"""

# 匹配子句关键字，忽略大小写和换行
clause_pattern = re.compile(
    r'(SELECT|FROM|WHERE|GROUP\s+BY|HAVING|ORDER\s+BY)',
    re.IGNORECASE | re.MULTILINE
)

# 拆分SQL为关键字和对应内容的列表
split_parts = clause_pattern.split(sql)

# 整理成结构化子句，过滤空内容
clause_dict = {}
for i in range(1, len(split_parts), 2):
    keyword = split_parts[i].strip()
    content = split_parts[i+1].strip()
    clause_dict[keyword] = f"{keyword} {content}"

# 按顺序输出拆分结果
for idx, clause_content in enumerate(clause_dict.values(), 1):
    print(f"{idx}.{clause_content}\n")

代码说明

使用re.IGNORECASE兼容关键字的大小写差异（如Group by和GROUP BY）。
通过split方法将SQL按关键字拆分，奇数位为关键字，偶数位为对应子句内容。
最后整理成结构化子句并按顺序输出，保留原SQL的格式。

注意事项

该方案适用于无嵌套子查询、无复杂注释的SQL语句。如果SQL包含子查询（如SELECT ... FROM (SELECT ...) AS t），正则会误拆分内部关键字，此时建议使用专业SQL解析库（如sqlparse）。
若存在注释，需先去除注释再拆分，避免干扰关键字匹配。

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

如何用正则表达式拆分复杂SELECT语句的各SQL子句

需求说明 #

示例SQL #

目标拆分效果 #

正则实现方案 #