以分隔符截取字段

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>