PostgreSQL中如何提取XML文本字段内特定字符串后的指定长度字符

你之前直接用substring操作失败，大概率是硬写了固定截取位置，XML内其他节点长度变化会导致<code1>标签的位置偏移，推荐优先用以下两种方案：

方案1：数据库原生XML解析函数提取（优先推荐） #

核心逻辑是先将TEXT类型字段强转为XML类型，再通过XPATH语法定位<code1>节点的文本内容，最后截取前4位即可，该方案不会受其他节点内容长度影响，稳定性最高。不同数据库的语法略有差异：

• MySQL 示例：

-- 提取code1标签后前4位
SELECT SUBSTRING(ExtractValue(CAST(DATA AS XML), '//code1/text()'), 1, 4) AS code1_4
FROM 你的表名;

• Oracle 示例：

SELECT SUBSTR(XMLTYPE(DATA).EXTRACT('//code1/text()').GETSTRINGVAL(), 1, 4) AS code1_4
FROM 你的表名;

• PostgreSQL 示例：

SELECT SUBSTRING((xpath('//code1/text()', XMLPARSE(DOCUMENT DATA)))[1]::text, 1, 4) AS code1_4
FROM 你的表名;

提取到编码后直接关联另一张表做比对即可，关联查询示例（以MySQL为例）：

SELECT a.*, b.*
FROM 存储XML的表 a
INNER JOIN 待比对的表 b 
ON SUBSTRING(ExtractValue(CAST(a.DATA AS XML), '//code1/text()'), 1, 4) = b.对应编码字段;

方案2：正则匹配提取（适合XML格式不规范的场景） #

如果你的XML格式存在语法问题无法正常解析，可以用正则先定位<code1>标签的位置再截取，比硬写substring位置的准确率高很多：

• MySQL 示例：

-- <code1>标签本身占6个字符，从第7位开始取4位即为标签后的前4位
SELECT SUBSTRING(REGEXP_SUBSTR(DATA, '<code1>([^<]+)</code1>'), 7, 4) AS code1_4
FROM 你的表名;

关联查询的逻辑和方案1完全一致，把提取逻辑替换到关联条件中即可。

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