Search code examples
mysqlregexposixpcre

Convert PHP compatible Regular Expression to MySql compatible regular expression - To extract Australian Mobile Numbers

I have a Regular expression which is running very well in PHP code. It throws errors when I am using it with MYSQL query.

^(?:(?:\+|0{0,2})61(\s*[\-]\s*)?|[0]?)?[4]\d{8}$

Please help me to convert it into MYSQL compatible regular expression.

Solution

  • You can use below query to extract Australian Mobile Numbers (starts with 4, +61, 61, 04, etc.) in MySQL

    ^(([+]|0{0,2})61([[:space:]]*-[[:space:]]*)?|0?)?4[0-9]{8}$

    Details:

    • ^ - start of string
    • ( - start of the first group:
      • ([+]|0{0,2}) - Group 2 matching + or zero, one or two zeros
      • 61 - 61 string
      • ([[:space:]]*-[[:space:]]*)? - an optional Group 3 matching a hyphen enclosed with zero or more whitespaces
    • | - or
      • 0? - an optional zero
    • )? - end of the first group, that is optional due to ?
    • 4[0-9]{8} - 4 and then any eight digits
    • $ - end of string.