SQL extract pattern from string with RegExp

Question

I am currently trying to extract a pattern from a string using MySQL but unfortunately did not manage to achieve the aspired result yet. Therefore I would love to get some support from you.

The string looks like this:

AB3530A/AB476-1

I want to extract only this part:

AB476

I already tried the following RegExp:

[A-Z]{2}[0-9]{3}

which returned:

AB476-

I also tried:

[A-Z]{2}[0-9]{3}([^0-9]|$) 

which returned AB353 and AB476 at http://regexr.com/ but only returns AB353 in MariaDB.

Does anybody know how to only extract without any additional signs?

Thanks in advance.

Best regards

Answer 1

This link shows that since its version 10.0.5, MariaDB supports Perl compatible regexp (“PCRE”).

This construct should then work SELECT REGEXP_SUBSTR(your_string, '\\b[A-Z]{2}\\d{3}\\b');.

(\b is "word boundary")

SELECT REGEXP_SUBSTR('AB3530A/AB476-1','\\b[A-Z]{2}\\d{3}\\b'); should return 'AB476'

NB: instead of [A-Z], which allows only for ASCII characters, you could use \\p{Lu}, which means “any character tagged as a letter by Unicode, and only the uppercase ones”. But I guess [A-Z] will be enough in your case.