MySQL REGEXP not matching string

Question

I have a table of messages. I am trying to find messages in the table that have an ID code which complies with a specific format. The regexp that I have below was written for matching these values in PHP, but I want to move it to a MySQL query.

It is looking for a specific format of an identifier code that looks like this:

[692370613-3CUWU]

The code has a consistent format:

• starts and ends with hard brackets [ ]
• two components inside,
  • first is an account number, min 9 digits, but could be higher
  • second component is a alphanumeric code, 5 characters, can include 1-9, and capital letters excluding "O"
• the complete code can occur anywhere in the message

I have a query that reads:

SELECT * FROM messages 
WHERE 
    msgBody REGEXP '\\[(\d){9,}-([A-NP-Z1-9]){5}\\]' 
      OR 
    msgSubject REGEXP '\\[(\d){9,}-([A-NP-Z1-9]){5}\\]'

I created a test row in the table which has only the sample value above in the msgBody field for testing - but it does not return any results.

I am guessing that I am missing something in the conversion of PHP style regex vs. MySQL.

Help is greatly appreciated.

Thank you!

Answer 1

SELECT * FROM messages 
WHERE 
  msgBody    REGEXP '\\[([0-9]){9,}-([A-NP-Z1-9]){5}\\]' 
             OR
  msgSubject REGEXP '\\[([0-9]){9,}-([A-NP-Z1-9]){5}\\]'

Answer 2

Instead of \d try using [[:digit:]]