CODEWITHSUNDEEP
mysqlregex
DanimalReks
DanimalReks

Reputation: 331

MySQL REGEX - Extract Data from String

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.

Upvotes: 0

Views: 9197

Answers (1)

Rick James
Rick James

Reputation: 142208

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.

Upvotes: 5

Related Questions