如下三句赋值即可
city:= SUBSTR(addr,1,INSTR(addr,'-',1,1)-1);
road:= SUBSTR(addr,INSTR(addr,'-',1,1)+1,INSTR(addr,'-',1,2)-INSTR(addr,'-',1,1)-1)
num := SUBSTR(addr,INSTR(addr,'-',1,2)+1,LENGTH(addr)-INSTR(addr,'-',1,2))
city:= SUBSTR(addr,1,INSTR(addr,'-',1,1)-1);
road:= SUBSTR(addr,INSTR(addr,'-',1,1)+1,INSTR(addr,'-',1,2)-INSTR(addr,'-',1,1)-1)
num := SUBSTR(addr,INSTR(addr,'-',1,2)+1,LENGTH(addr)-INSTR(addr,'-',1,2))
substr('西安市-兴庆路-100号',instr('西安市-兴庆路-100号','-',1,1)+1,instr('西安市-兴庆路-100号','-',1,1)-1),
substr('西安市-兴庆路-100号',instr('西安市-兴庆路-100号','-',1,2)+1,instr('西安市-兴庆路-100号','-',1,1))
from dual
2 addr varchar2(200):='城市名-街道名-門牌號';
3 city varchar2(200);
4 road varchar2(200);
5 num varchar2(200);
6 begin
7 city := substr(addr,1,instr(addr,'-')-1);
8 addr := substr(addr,instr(addr,'-')+1,length(addr));
9
10 road := substr(addr,1,instr(addr,'-')-1);
11 num := substr(addr,instr(addr,'-')+1,length(addr));
12
13 dbms_output.put_line('city is:'||city||',road is:'||road||',num is:'||num);
14 end;
15 /
city is:城市名,road is:街道名,num is:門牌號PL/SQL 程序順利完成目前歷時: 00:00:00.16