如何从CLOB列中的JSON文本提取任意IP地址？

你的问题出在递归查询的关联逻辑上，原代码没有限制仅在当前行内递归，导致跨行关联产生无限循环或无有效结果。以下是修正后的方案：

修正后的查询语句 #

假设你的表有唯一标识（比如ROWID，如果有主键也可以用主键字段），可以用以下语句提取所有IP：

SELECT DISTINCT
  TRIM(REGEXP_SUBSTR(
    REGEXP_REPLACE(json, '[^0-9.]', ' '),
    '(?:\d{1,3}\.){3}\d{1,3}',
    1,
    LEVEL
  )) AS extracted_ip
FROM
  kg_json_tst
CONNECT BY LEVEL <= REGEXP_COUNT(REGEXP_REPLACE(json, '[^0-9.]', ' '), '(?:\d{1,3}\.){3}\d{1,3}')
  AND PRIOR ROWID = ROWID
  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
WHERE TRIM(REGEXP_SUBSTR(REGEXP_REPLACE(json, '[^0-9.]', ' '), '(?:\d{1,3}\.){3}\d{1,3}', 1, LEVEL)) IS NOT NULL;

关键修正点 #

• 限制行内递归：添加PRIOR ROWID = ROWID，确保递归仅在当前行的CLOB内容中进行，避免跨行关联导致的无限循环或无效结果。
• 控制递归层级：用REGEXP_COUNT获取当前行中IP的数量，直接限制LEVEL的最大值，比原代码的判断逻辑更高效。
• 去重与清理：DISTINCT去除同一行内重复的IP，TRIM清理正则替换后可能残留的空格。
• 正则优化：去掉\b，Oracle的正则单词边界\b行为与其他语言存在差异，直接匹配IP格式更稳定。

更严格的IP格式匹配（可选） #

如果需要确保提取的是合法IP（每个段0-255），可以替换正则表达式为更精确的版本：

'(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)'

替换后的完整语句：

SELECT DISTINCT
  TRIM(REGEXP_SUBSTR(
    REGEXP_REPLACE(json, '[^0-9.]', ' '),
    '(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)',
    1,
    LEVEL
  )) AS extracted_ip
FROM
  kg_json_tst
CONNECT BY LEVEL <= REGEXP_COUNT(REGEXP_REPLACE(json, '[^0-9.]', ' '), '(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)')
  AND PRIOR ROWID = ROWID
  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
WHERE TRIM(REGEXP_SUBSTR(REGEXP_REPLACE(json, '[^0-9.]', ' '), '(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)', 1, LEVEL)) IS NOT NULL;

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