RegExp Issue in MySQL Statement

Question

I have the following RegExp in mySQL:

WHERE telephone NOT REGEXP'(^[+][0-9]+\ )?([0-9]{3}\-[0-9]{3}\-[0-9]{4})(\ x[0-9]+$)?'

This is supposed to select all phone numbers that are not in the +0 000-000-0000 x000 format. It works almost perfectly except I have one customer that entered in 000-000-0000</span> into the telephone field and this expression does not find it. I know I have it set to accept an optional extension, but how do I make sure that optional extension is in the correct format?

Answer 1

Add an ending anchor to what you have:

^([+][0-9]+\ )?([0-9]{3}\-[0-9]{3}\-[0-9]{4})(\ x[0-9]+)?$

Demo