mysql regexp 数字,MySQL REGEXP-删除空格和非数字字符

I want to do a search in a MySQL database for phone numbers.

At present with this query:

SELECT person FROM people WHERE phone_number RLIKE '123456789'

It wont find:

(123) 456 789

(12) 456789

123 456 789

etc etc

In my MySQL query is it possible to strip everything and just leave the numbers?

I'd also like to search removing all spaces and just leaving the numbers and characters and also removing all characters and just leaving numbers and spaces. Not sure how easy all this is.

Appreciate your help!

解决方案

how about:

SELECT

person,

replace(replace(replace(replace(phone_number,' ',''),'(',''),')',''),'-','') as phone_number

FROM

people

WHERE

phone_number RLIKE '^[+]?[-() 0-9]+$';

matches numbers that start with a plus sign, they may contain hyphens, parenthesis and spaces. but no plus signs other than at the start. and also no characters. also removes hyphens, spaces and parenthesis.