CODEWITHSUNDEEP
mysqlregex
aSystemOverload
aSystemOverload

Reputation: 3084

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

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

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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;

Upvotes: 1

Related Questions