Oracle 12.x如何准确筛选CLOB列中的XML与JSON数据

筛选存储XML数据的行 #

要避免JSON中含<导致的误判，关键是验证CLOB的首个非空白字符为'<''，以下两种方法都能实现：

方法1：正则匹配开头模式 #

直接通过正则表达式匹配“开头允许任意空白，随后是'<''”的CLOB：

SELECT *
FROM DB.COLUMN
WHERE REGEXP_LIKE(CLOB_COLUMN, '^[\s]*<.*')

说明：^对应字符串起始位置，[\s]*匹配0个或多个空白字符（空格、换行、制表符都算），<精准匹配XML的起始标记，.*匹配后续所有内容。

方法2：精准定位首个非空白字符 #

如果担心正则表达式的性能开销，可以用函数组合精准提取首个非空白字符判断：

SELECT *
FROM DB.COLUMN
WHERE DBMS_LOB.SUBSTR(CLOB_COLUMN, 1, REGEXP_INSTR(CLOB_COLUMN, '[^\s]')) = '<'

说明：REGEXP_INSTR(CLOB_COLUMN, '[^\s]')找到第一个非空白字符的位置，再用DBMS_LOB.SUBSTR提取该位置的1个字符，判断是否为'<''。

筛选存储JSON数据的行 #

有两种思路，推荐优先用Oracle内置的JSON验证函数，准确性更高：

方法1：基于JSON起始特征匹配 #

和XML的思路对应，匹配“跳过空白后以{或[开头”的CLOB：

SELECT *
FROM DB.COLUMN
WHERE REGEXP_LIKE(CLOB_COLUMN, '^[\s]*([{\[]).*')

说明：([{\[])匹配JSON对象（{）或数组（[）的起始符号，确保跳过开头空白后是合法的JSON起始标记。

方法2：用JSON_VALID验证合法性 #

Oracle 12c及以上提供的JSON_VALID函数可以直接校验CLOB是否为合法JSON，完全不受字符串中含<的影响：

SELECT *
FROM DB.COLUMN
WHERE JSON_VALID(CLOB_COLUMN) = TRUE

说明：函数返回TRUE表示输入是合法JSON，FALSE则不是，能精准区分JSON和其他格式数据。

原方法的问题 #

你之前用DBMS_LOB.INSTR判断是否包含<的逻辑存在明显缺陷：JSON的字符串字段里可能包含<（比如{"desc": "<div>内容</div>"}），这时INSTR会检测到<，导致JSON行被误判为XML行；反过来，如果XML开头有大量空白，原逻辑虽然能命中，但核心的误判问题无法解决。

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