算法如下。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: 0mysql> 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)
-> 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: 0mysql> 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 `crm`.`test`$$CREATE DEFINER=`root`@`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');