MySQL REGEX-从字符串中提取数据 [英] MySQL REGEX - Extract Data from String

问题描述

我被困住了...我在MySQL中有一个很长的字符串字段(描述).我需要使用视图将其提取到另一个字段中，这是一个嵌入在该字符串中的参考数字.字符串看起来像这样.

I am stuck... I have a field (Description) in MySQL with a very long string. Embedded in that string is a reference number that I need to extract to another field using a view. The string will look something like this.

LOREM IPSUM DOLOR SIT AMET CONSECTETUR ADITSCING ELIT整数NEC ODIO XX00000000X LIBERO SED曲线ANTE DAPIBUS DIAM SED NISI NULLA QUIS SEM在NIBH ELEMENTUM IMPERDIET

LOREM IPSUM DOLOR SIT AMET CONSECTETUR ADIPISCING ELIT INTEGER NEC ODIO XX00000000X LIBERO SED CURSUS ANTE DAPIBUS DIAM SED NISI NULLA QUIS SEM AT NIBH ELEMENTUM IMPERDIET

我从该字符串中需要的是XX00000000X.它始终以两个字母开头，中间以数字开头，并以字母结尾.

What I need from that string is XX00000000X. It always starts with two letters, numbers in the middle and ends with a letter.

我有以下查询:

SELECT 
    Description, 
    SUBSTRING_INDEX (Description, ' ',  (Description REGEXP '[[:upper:]]{1,2}[[:digit:]]+[[:upper:]]$') * -1 ) AS Reference 
FROM db_test.tbl_regex;

问题在于，它仅在描述"字段的末尾收集参考数据.

The problem is that it only collects the Reference data when it is at the end of the Description field.

推荐答案

REGEXP_SUBSTR()在MariaDB 10.0.5和MySQL 8.0中引入.

REGEXP_SUBSTR() was introduced in MariaDB 10.0.5 and MySQL 8.0.

这就是您需要查找并提取XX00000000X的原因.

That is what you need to locate and extract the XX00000000X.

REGEXP(如您所用)仅返回true/false. SUBSTRING_INDEX()需要字符串.

REGEXP (as you used it) only returns true/false. SUBSTRING_INDEX() needs the string.

如果您不能升级到其中之一，则最好的方法是使用REGEXP标识具有XX00000000X的行，然后使用您的应用代码将其提取.

If you can't upgrade to one of those, the best you can do is use REGEXP to identify rows that have XX00000000X, then use your app code to extract it.

这篇关于MySQL REGEX-从字符串中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！