如何从含前置注释的字符串提取SQL查询并完成数据分类

核心需求 #

• 从带前置注释的字符串中提取SQL查询，保留查询后的所有注释，忽略查询前的注释
• 对数据分类：判断是否为可提取的SQL语句（仅含注释的内容不提取）

示例展示 #

输入输出示例1 #

输入：

\n--comment
--comment
Select *from test
--comment

输出：

Select *from test
--comment

输入输出示例2 #

输入：

/*comment*/
Select*from test

输出：

Select*from test

输入输出示例3 #

输入：

/*comment*/
--comment
Select*from test
--comment
/*Comment*/

输出：

Select*from test
--comment
/*Comment*/

分类示例表格 #

输入表格

输出表格

当前问题 #

尝试通过判断注释位置的方法处理，但未达到预期效果。现有仅针对--的处理代码无法正确移除单行/多行前置注释：

WITH cleaned_data AS (
    SELECT 
        id,
        -- Clean leading CR (chr(13)) and LF (chr(10))
        LTRIM(TRIM(LEADING chr(10) FROM TRIM(LEADING chr(13) FROM data))) AS cleaned_statement
    FROM abc
),
extracted_comments AS (
    SELECT 
        id,
        -- Extract relevant SQL statement or comment
        CASE 
            -- If there is a single-line comment
            WHEN POSITION('--' IN cleaned_statement) > 0 THEN
                -- Extract everything from the first occurrence of '--' to the end of the line
                TRIM(SUBSTRING(cleaned_statement FROM POSITION('--' IN cleaned_statement)))
            -- If no comments found, return the cleaned statement
            ELSE cleaned_statement
        END AS final_statement
    FROM cleaned_data
)
SELECT final_statement
FROM extracted_comments
WHERE final_statement IS NOT NULL
  AND final_statement != '';

解决方案 #

思路说明 #

分三步处理：

1. 清理字符串开头的换行、回车、空格等空白字符
2. 递归移除所有前置注释：先处理多行注释/*...*/，再处理单行注释--，直到遇到非注释的有效内容
3. 分类判断：排除空内容或仅含注释的情况，标记有效SQL为Extract

实现代码（PostgreSQL） #

WITH recursive cleaned_data AS (
    -- 清理开头的换行、回车和空格
    SELECT 
        id,
        LTRIM(TRIM(LEADING chr(10) || chr(13) FROM data)) AS cleaned_statement
    FROM abc
),
-- 递归移除所有前置多行注释 /*...*/
remove_multiline_comments AS (
    SELECT 
        id,
        cleaned_statement AS stmt,
        1 AS iteration
    FROM cleaned_data
    UNION ALL
    SELECT 
        id,
        CASE
            WHEN stmt LIKE '/*%' THEN
                LTRIM(SUBSTRING(stmt FROM POSITION('*/' IN stmt) + 2))
            ELSE stmt
        END AS stmt,
        iteration + 1
    FROM remove_multiline_comments
    WHERE stmt LIKE '/*%' AND iteration < 10 -- 限制递归次数防死循环
),
after_multiline AS (
    SELECT DISTINCT ON(id) id, stmt
    FROM remove_multiline_comments
    ORDER BY id, iteration DESC
),
-- 递归移除所有前置单行注释 --...
remove_singleline_comments AS (
    SELECT 
        id,
        stmt AS processed_stmt,
        1 AS iteration
    FROM after_multiline
    UNION ALL
    SELECT 
        id,
        CASE
            WHEN processed_stmt LIKE '--%' THEN
                -- 跳过当前单行注释行，取后续内容
                LTRIM(SUBSTRING(processed_stmt FROM POSITION(chr(10) IN processed_stmt) + 1))
            ELSE processed_stmt
        END AS processed_stmt,
        iteration + 1
    FROM remove_singleline_comments
    WHERE processed_stmt LIKE '--%' AND iteration < 10 -- 限制递归次数
),
final_processed AS (
    SELECT DISTINCT ON(id) id, processed_stmt AS extracted_sql
    FROM remove_singleline_comments
    ORDER BY id, iteration DESC
),
-- 分类判断
categorized AS (
    SELECT 
        id,
        extracted_sql,
        CASE
            WHEN extracted_sql IS NULL OR extracted_sql = '' THEN ''
            WHEN extracted_sql LIKE '--%' AND POSITION(chr(10) IN extracted_sql) = 0 THEN ''
            WHEN extracted_sql LIKE '/*%' AND POSITION('*/' IN extracted_sql) = 0 THEN ''
            ELSE 'Extract'
        END AS categorization
    FROM final_processed
)
SELECT 
    extracted_sql AS final_statement,
    categorization
FROM categorized
WHERE extracted_sql IS NOT NULL;

代码说明 #

1. 多行注释处理：通过递归循环移除所有开头的闭合多行注释，处理后自动清理开头空白
2. 单行注释处理：递归跳过所有开头的单行注释行，直到遇到非注释内容
3. 分类逻辑：
   • 空内容、仅单行注释、未闭合的多行注释均标记为空
   • 其余包含有效SQL的内容标记为Extract

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