DELIMITER $$

DROP function IF EXISTS `getPhone` $$

CREATE DEFINER=`root`@`%` FUNCTION `getPhone` (f_string varchar(1000) CHARACTER SET utf8mb4) RETURNS VARCHAR(11)

BEGIN

DECLARE isPhone INT(1) DEFAULT 0;

DECLARE ipos INT(11) DEFAULT 0;

DECLARE phone VARCHAR(11) DEFAULT '';

DECLARE c_char VARCHAR(1) CHARACTER SET utf8mb4 DEFAULT '';

SELECT f_string REGEXP "[1][35678][0-9]{9}" INTO isPhone;

IF (isPhone=1) THEN

f_loop:LOOP

SELECT SUBSTR(f_string,ipos,1) INTO c_char;

IF (c_char='1') THEN

SELECT SUBSTR(f_string,ipos,11) INTO phone;

SELECT phone REGEXP "[1][35678][0-9]{9}" INTO isPhone;

IF (isPhone=1) THEN

LEAVE f_loop;

ELSE

SET ipos = ipos+11;

END IF;

END IF;

SET ipos = ipos+1;

IF ipos=LENGTH(f_string) THEN

LEAVE f_loop;

END IF;

END LOOP f_loop;

END IF;

RETURN phone;

END$$

DELIMITER;

SELECT getPhone("/你好～13952225212/你好");


今天和大家分享一下如何使用mysql语句从用户手机号中提取纯号码。

最近工作中需要查询一些用户的手机号，但是数据库中的手机号存在不太规范的情况，数据大概有以下几种格式。

以上都是11位的电话号码几种常见的形式，我们使用mysql语句进行查询：select phone from table where length(phone) = 11。此时的结果为

只有这一种形式的号码数据被成功提取，其他数据就很尴尬地漏掉了。然鹅，由于本人比较懒，不想写提取纯数字的函数，就开始捣鼓各种查询语句。

把mysql数据库中的各种length函数来了一遍，完全没用，长度不是11的仍旧不是11。怎么办呢？

观察了一下电话号码，主要是‘+‘，‘-’，空格以及’86’占了长度，纯数字提不出来，我们可以换向思考，把这些碍事的东西去掉。

Mysql中有replace函数，我们先将空格，’-‘和‘+’用该函数去除。这里有一点需要特别注意，86可能是出现在真实电话号码中的数字，因此不能简单的使用replace(phone,'86 ','')，否则可能会出现九位数的手机号码数据。

由于replace不能一次替换多个内容，故我们采取嵌套方案，语句为：

select phone,replace(replace(replace(phone,'-',''),' ',''),'+','') as phone_change from table;替换后结果如下图。

可以看到，此时电话号码为纯数字了。我们用条件语句(case when)和截断函数(substring)来去掉多余的86，抽取纯手机号。

select phone,

case when

replace(replace(replace(phone,'-',''),' ',''),'+','') like '86%' thensubstring(replace(replace(replace(phone,'-',''),' ',''),'+',''),3)else replace(replace(replace(phone,'-',''),' ',''),'+','') end as change_phone

from table;

至此，所有手机号都已正常。