mysql 提取字符串数据,MySQL REGEX-从字符串中提取数据

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 ADIPISCING ELIT INTEGER NEC ODIO XX00000000X LIBERO SED CURSUS ANTE DAPIBUS DIAM SED NISI NULLA QUIS SEM AT NIBH ELEMENTUM IMPERDIET

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

I have the following query:

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() was introduced in MariaDB 10.0.5 and MySQL 8.0.

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

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

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.