MySQL REGEXP-删除空格和非数字字符 [英] MySQL REGEXP - Removing white space and non-numeric characters

问题描述

我想在MySQL数据库中搜索电话号码.

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'

找不到:

(123) 456 789
(12) 456789
123 456 789
etc etc

在我的MySQL查询中，是否可以剥离所有内容并仅保留数字?

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.

感谢您的帮助！

推荐答案

如何:

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.

这篇关于MySQL REGEXP-删除空格和非数字字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！