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?

What if you change (\ x[0-9]+$)? to (\ x[0-9]+)?$ ? You may also want to change (^[+][0-9]+\ )? to ^([+][0-9]+\ )? — MDEV
– MDEV, Commented May 2, 2015 at 17:15
Cleanse your data of </span> (etc) before storing into a table. — Rick James
– Rick James, Commented May 4, 2015 at 0:10
@rick LOL that's the whole reason I wrote the function. I to clean out all the crap people have put in and not allow anything like it in the future. — user3167249
– user3167249, Commented May 4, 2015 at 2:07

dawg · Accepted Answer · 2015-05-02 17:33:00Z

1

Add an ending anchor to what you have:

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

Demo

edited May 2, 2015 at 17:33

answered May 2, 2015 at 17:18

dawg

105k24 gold badges142 silver badges217 bronze badges

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

Collectives™ on Stack Overflow

RegExp Issue in MySQL Statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related