mysql提取手机号,mysql 正值表达式提取字符串中的手机号码

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("/鄙视～13912890902/鄙视转微19812340909 13812340909 xxx");