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");
评论
登录后参与技术讨论,分享你的经验与见解。
登录 / 注册暂无评论