1 查看表数据
SQL> select * from test1;
ID NAME
---------- ----------------------------------------------------------------
1 zhengjiang/hangzhou/bingjiang
2 taizhou/sanmen/xiaoxiong
3 嘉兴市/海宁县/许巷镇
2 查看分隔后数据
select substr(t.name, 1, instr(t.name, '/') - 1),
substr(t.name,
instr(t.name, '/') + 1,
instr(t.name, '/', 1, 2) - instr(t.name, '/') - 1),
substr(t.name, instr(t.name, '/', 1, 2) + 1)
from test1 t
SQL> select substr(t.name, 1, instr(t.name, '/') - 1),
2 substr(t.name,
3 instr(t.name, '/') + 1,
4 instr(t.name, '/', 1, 2) - instr(t.name, '/') - 1),
5 substr(t.name, instr(t.name, '/', 1, 2) + 1)
6 from test1 t;
SUBSTR(T.NAME,1,INSTR(T.NAME,' SUBSTR(T.NAME,INSTR(T.NAME,'/' SUBSTR(T.NAME,INSTR(T.NAME,'/'
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
zhengjiang hangzhou bingjiang
taizhou sanmen xiaoxiong
嘉兴市 海宁县 许巷镇
SQL>