How to match specific string pattern using REGEXP (or similar) in MySQL

Question

I'm using the below UPDATE statement to update a flag that confirms if a Code is correctly formatted, note the code can be anywhere in 'RefCode'. This works in Excel, but I understand MySQL REGEX is a little different to standard REGEX:

UPDATE tblRequests
SET flagIsRefCodeOK= (RefCode REGEXP '^[A-Z0-9]{8}-(?:[A-Z0-9]{4}-){3}[A-Z0-9]{12}$')
WHERE DataSetID=11;

In a nut shell, it should be true/[1] if the field contains ddda999d-99de-999e-999e-9b9bf9999999:

8 alphanumeric characters
A SINGLE DASH
4 alphanumeric characters
A SINGLE DASH
4 alphanumeric characters
A SINGLE DASH
4 alphanumeric characters
A SINGLE DASH
12 alphanumeric characters

Would appreciate any assistance with this.

Thnx

zx81 · Accepted Answer · 2014-07-30 12:26:15Z

2

In MySQL you cannot use (?: non-capture groups )

Do something like this:

UPDATE mytable
SET flag = `1`
WHERE mycolumn REGEXP "^[[:alnum:]]{8}-([[:alnum:]]{4}-){3}[[:alnum:]]{12}$"

Note that the POSIX class [:alnum:] matches ASCII letters a-z, A-Z and digits 0-9

edited Jul 30, 2014 at 12:26

answered Jul 30, 2014 at 12:20

zx81

42k10 gold badges92 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

zx81 Over a year ago

FYI Updated to use POSIX class alnum which matches ASCII digits and letters

aSystemOverload Over a year ago

That's great, can we tweak so that it will pick up that pattern among other text. ie: //The Code is ddda999d-99de-999e-999e-9b9bf9999999!//

zx81 Over a year ago

Sorry for the delay, it was night time. Yes! All we need to do is remove the start and end anchors: (the caret ^ and dollar $at the ends) ...REGEXP "[[:alnum:]]{8}-([[:alnum:]]{4}-){3}[[:alnum:]]{12}" Let me know if you need more tweaks. :)

Collectives™ on Stack Overflow

How to match specific string pattern using REGEXP (or similar) in MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related