时间戳

首页 > 解决方案 > MySQL 5.5 - 如何从具有重复字符串的字段中提取部分文本？

mysql - MySQL 5.5 - 如何从具有重复字符串的字段中提取部分文本？

问题描述

我有这样的字段：

UPDATE</transactionType><column><name>prio</name><newValue>5</newValue><oldValue>1</oldValue><newValue>aaa<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>
UPDATE</transactionType><column><name>prio</name><newValue>51</newValue><oldValue>11</oldValue><newValue>bbb<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>

我正在尝试<newValue>从左侧首先提取文本。它将是一个或两个数字/字母。另外，同时我想<oldValue>先从左边看。所以结果是：

newValue oldValue
5        1
51       11

标签： mysql

解决方案

由于它是一个不完整的 XML，让我们使用简单的字符串函数。

LOCATE可以找到子字符串的位置。

LEFT从开始到某个位置获取一个子字符串。

从该子字符串中，SUBSTRING_INDEX函数可以方便地获取最终标记之后的字符。

示例代码：

-- test table
drop table if exists YourTable;
create table YourTable (col varchar(1000));

-- Sample data
insert into YourTable (col) values
('UPDATE</transactionType><column><name>prio</name><newValue>5</newValue><oldValue>1</oldValue><newValue>aaa<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>'),
('UPDATE</transactionType><column> <name>prio</name><newValue>51</newValue><oldValue>11</oldValue><newValue>bbb<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>');

-- Query
SELECT 
 SUBSTRING_INDEX(LEFT(col, LOCATE('</oldValue>', col)-1),'>',-1) AS oldValue,
 SUBSTRING_INDEX(LEFT(col, LOCATE('</newValue>', col)-1),'>',-1) AS newValue
FROM YourTable;

结果：

oldValue    newValue
1           5
11          51

对reextester的测试在这里

边注：

在 MySql 8 中，您也可以为此使用REGEXP_SUBSTR。 

SELECT  
REGEXP_SUBSTR(col,'(?<=<oldValue>)[^<>]*(?=</oldValue)',1,1) as oldValue,
REGEXP_SUBSTR(col,'(?<=<newValue>)[^<>]*(?=</newValue>)',1,1) as newValue
FROM YourTable;

对db<>fiddle的测试在这里

（但请保持沉默。有些人会因为您使用正则表达式解析 XML 而皱眉。Fe here。
但话又说回来，无效的 XML 并不是真正的 XML）

推荐阅读