我的问题是这样的:数据库表A里原来有这么一个字段(departname),这个字段里面的数据是这种格式的(aa/bb/cc),现在发生了变化在这个表里又加了两个字段(departname2,departname3),要求把以前的数据拆分并更新到相应的departname2,departname3字段里,最后的结果:departname2=bb,departname3=cc,departname=aa;如何写这个sql语句,我的数据库是oracle
调试欢乐多
SQL> SELECT SUBSTR(VAR||'/',1,INSTR(VAR||'/','/',1,1)-1) "DEPARTMENT",
2 SUBSTR(VAR||'/',INSTR(VAR||'/','/',1,1)+1,INSTR(VAR||'/','/',1,2)-INSTR(VAR||'/','/',1,1)-1) "DEPARTMENT2",
3 SUBSTR(VAR||'/',INSTR(VAR||'/','/',1,2)+1,INSTR(VAR||'/','/',1,3)-INSTR(VAR||'/','/',1,2)-1) "DEPARTMENT3"
4 FROM (SELECT 'aa/bb/cc' VAR FROM DUAL
5 UNION ALL
6 SELECT 'aaA/bbB/ccC' VAR FROM DUAL
7 UNION ALL
8 SELECT 'aaYY/bbDD/ccWW' VAR FROM DUAL
9 );DEPARTMENT DEPARTMENT2 DEPARTMENT3
--------------- --------------- ---------------
aa bb cc
aaA bbB ccC
aaYY bbDD ccWWSQL>