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!

codaddict · Accepted Answer · 2012-09-14 17:26:40Z

1

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

answered Sep 14, 2012 at 17:26

codaddict

457k83 gold badges501 silver badges537 bronze badges

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

Comments

Ωmega · Accepted Answer · 2012-09-14 17:33:31Z

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}\\]'

answered Sep 14, 2012 at 17:33

Ωmega

44k35 gold badges143 silver badges213 bronze badges

Collectives™ on Stack Overflow

MySQL REGEXP not matching string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related