mysql - Got error 'repetition-operator operand invalid' from regexp (Error #1139) -

i have column phone_number on database entry may contain more 1 phone number. plan identify entries not pass regex expression validation.

this query using accomplish objective:

 select id, phone_number store phone_number not regexp '^\s*\(?(020[78]?\)? ?[1-9][0-9]{2,3} ?[0-9]{4})|(0[1-8][0-9]{3}\)? ?[1-9][0-9]{2} ?[0-9]{3})\s*$'; 

problem is, every time run code, error:

error code: 1139. got error 'repetition-operator operand invalid' regexp

thanks in advance.

the regex using has @ least 2 issues: 1) escapes should doubled, , 2) there 2 groups separated | makes ^ , $ apply 2 branches separately.

'^\s*\(?(020[78]?\)? ?[1-9][0-9]{2,3} ?[0-9]{4})|(0[1-8][0-9]{3}\)? ?[1-9][0-9]{2} ?[0-9]{3})\s*$'         ^--------------------------------------^ ^------------------------------------------^ 

you can use

'^[[:space:]]*\\(?(020[78]?\\)? ?[1-9][0-9]{2,3} ?[0-9]{4}|0[1-8][0-9]{3}\\)? ?[1-9][0-9]{2} ?[0-9]{3})[[:space:]]*$' 

breakdown:

• ^ - start of string
• [[:space:]]* - 0+ whitespaces
• \\(? - 1 or 0 ( chars
• (020[78]?\\)? ?[1-9][0-9]{2,3} ?[0-9]{4}|0[1-8][0-9]{3}\\)? ?[1-9][0-9]{2} ?[0-9]{3}) - alternation group matching 2 alternatives:
  • 020[78]?\\)? ?[1-9][0-9]{2,3} ?[0-9]{4} - 020 + optional 7 or 8 + optional ) + optional space + digit 1 9 + 3 or 2 digits + optional space + 4 digits
  • | - or
  • 0[1-8][0-9]{3}\\)? ?[1-9][0-9]{2} ?[0-9]{3} - 0 + digit 1 8 + 3 digits + optional ) + optional space + digit 1 9 + 2 digits + optional space + 3 digits
• [[:space:]]* - 0+ whitespaces
• $ - end of string