Teradata中用REGEXP_SUBSTR提取所有数据源对象的方法咨询

在Teradata中，REGEXP_SUBSTR仅支持返回单个匹配结果，无法直接提取所有符合条件的数据源。要获取视图SQL中所有作为数据源的表或视图，可通过递归CTE或表生成函数拆分两种方式实现，以下是具体方案：

方法1：递归CTE遍历所有匹配项 #

通过递归CTE逐个定位正则匹配的位置，提取所有符合库.表格式的数据源对象，适配复杂的JOIN/逗号分隔场景：

WITH RECURSIVE SQL_Sources AS (
    -- 预处理SQL文本：移除换行、合并多空格、统一大写
    SELECT 
        TableId,
        TableName,
        UPPER(REGEXP_REPLACE(REGEXP_REPLACE(CAST(RequestText AS CLOB), '\n|\r', ' '), '[ ]{2,}', ' ')) AS Clean_SQL,
        1 AS Match_Pos
    FROM DBC.TablesV
    WHERE TableKind = 'V' -- 仅筛选视图对象
      AND RequestText IS NOT NULL

    UNION ALL

    -- 递归定位下一个匹配位置
    SELECT 
        TableId,
        TableName,
        Clean_SQL,
        REGEXP_INSTR(Clean_SQL, '(FROM|JOIN) \w+\.\w+', Match_Pos + 1, 1, 0) AS Match_Pos
    FROM SQL_Sources
    WHERE Match_Pos > 0
)
-- 提取所有有效数据源对象
SELECT 
    TableId,
    TableName,
    -- 捕获组2提取库.表名称
    REGEXP_SUBSTR(Clean_SQL, '(FROM|JOIN) (\w+\.\w+)', Match_Pos, 1, 0, 'i', 2) AS Source_Object
FROM SQL_Sources
WHERE Match_Pos > 0
ORDER BY TableId, Match_Pos;

方法2：表生成函数拆分简单场景 #

如果你的视图SQL仅使用逗号分隔或基础JOIN语法，可先提取FROM到WHERE/分号的片段，再拆分过滤：

SELECT 
    t.TableId,
    t.TableName,
    -- 清理拆分后的token，提取有效表名
    TRIM(REGEXP_REPLACE(s.token, '^(FROM|JOIN)|,|;', '')) AS Source_Object
FROM DBC.TablesV t
-- 拆分FROM到WHERE/分号之间的内容
CROSS JOIN STRTOK_SPLIT_TO_TABLE(
    t.TableId,
    REGEXP_SUBSTR(UPPER(REGEXP_REPLACE(CAST(RequestText AS CLOB), '\n|\r| {2,}', ' ')), 'FROM .*(?=WHERE|;)'),
    ' '
) s
WHERE t.TableKind = 'V'
  AND s.token REGEXP '\w+\.\w+' -- 仅保留库.表格式的对象
  AND s.token NOT IN ('FROM', 'JOIN', 'INNER', 'LEFT', 'RIGHT', 'FULL'); -- 排除JOIN相关关键字

正则表达式优化提示 #

• 处理别名：若存在FROM db.table t这类带别名的写法，可将正则调整为(FROM|JOIN) (\w+\.\w+)(?:\s+\w+)?，依然通过捕获组2提取表名。
• 排除子查询：若视图包含子查询，可先通过REGEXP_REPLACE(Clean_SQL, '\(SELECT.*?\)', '')移除括号内的子查询内容，避免误匹配。
• 忽略大小写：在正则函数中添加'i'参数（如REGEXP_SUBSTR(..., 'i')），兼容大小写混合的SQL写法。

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