mysql提取电话号码_mysql中多个前缀号码的提取方法

比如电话号码为：1234567(号码可能为8位或4位或7位)

前缀有：852，3，3852，852133

假如用户输入：31234567 ，在mysql中如何判断前缀是3？

或者用户输入：8521234567 。mysql> create table areacode( -> acode varchar(10) primary key -> ) -> -> ; Query OK, 0 rows affected (0.09 sec) mysql> insert into areacode values (852),(3),(3852),(852133); Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from areacode; +--------+ | acode | +--------+ | 3 | | 3852 | | 852 | | 852133 | +--------+ 4 rows in set (0.00 sec) mysql> select substr('31234567',max(length(acode))+1) -> from areacode -> where instr('31234567',acode)=1 ; +-----------------------------------------+ | substr('31234567',max(length(acode))+1) | +-----------------------------------------+ | 1234567 | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> select substr('38521234567',max(length(acode))+1) -> from areacode -> where instr('38521234567',acode)=1 ; +--------------------------------------------+ | substr('38521234567',max(length(acode))+1) | +--------------------------------------------+ | 1234567 | +--------------------------------------------+ 1 row in set (0.05 sec) DELIMITER $$ DROP PROCEDURE IF EXISTS `CallCentre`.`test`$$ CREATE DEFINER=`edmond`@`localhost` PROCEDURE `test`(DialedNumbers char(20)) begin Declare RealDial char(20); DECLARE RetVar char(20); select substr(DialedNumbers,max(length(acode))+1) into RealDial from areacode where instr(DialedNumbers,acode)=1 ; if not isnull(RealDial) then set RetVar=RealDial; else set RetVar=DialedNumbers; end if; select RetVar; end$$ DELIMITER ; call test('85210000')