CODEWITHSUNDEEP
sqlregexmariadb
ShortDive
ShortDive

Reputation: 177

SQL extract pattern from string with RegExp

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

Upvotes: 4

Views: 34177

Answers (2)

Sxilderik
Sxilderik

Reputation: 798

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.

Upvotes: 5

Pastor
Pastor

Reputation: 318

ShortDive!

Instead of Regex you can try this:

SELECT SUBSTR('AB3530A/AB476-1',9,5) constant_length
     , SUBSTRING_INDEX(SUBSTRING_INDEX('AB3530A/AB476-1','/',-1),'-',1) variable_length

Upvotes: -1

Related Questions