56,687
社区成员
发帖
与我相关
我的任务
分享
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');
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)