Correct format to use regex in MySQL SELECT statement (not within WHERE section)

Question

I need to use REGEX (or similar functiuonality) to update an existing table in order to identify if a string field contains a correctly formatted code:

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

But it doesn't seem to like the statement:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REGEX '^[A-Z0-9]

Note, this code will be added to a BEFORE INSERT trigger, which is responsible for updating a number of flags. I'm not fussed (much) with whether the REGEX is correct, especially within MySQL, I just want it to try to work. Then I'll figure out the exact REGEX if this doesn't work.

Thnx

@Strawberry apologies, I was replacing certain names for privacy, the first RefCode is a flag, I've now updated the example. Will check the other comments and update once I've tried. — aSystemOverload
– aSystemOverload, Commented Jul 30, 2014 at 12:00

aSystemOverload · Accepted Answer · 2014-07-30 12:04:42Z

1

The operator name is REGEXP not REGEX, so try:

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

edited Jul 30, 2014 at 12:04

aSystemOverload

3,12218 gold badges52 silver badges74 bronze badges

answered Jul 30, 2014 at 11:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Correct format to use regex in MySQL SELECT statement (not within WHERE section)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related