oracle 字段截取

Question

表a 中有字段
id     name
1      张三，李四
2      张三，李四，王五
3      李四查询出来的结果是id  name1 name2  name3
1   张三  李四
2   张三  李四   王五
3   李四  查询语句怎么写啊

Answer 1

select id, 
       substr(name,1,instr(name,',',1,1)-1) as name1,
       substr(name,instr(name,',',1,1)+1,instr(name,',',1,2)-instr(name,',',1,1)-1) as name2,
       substr(name,instr(name,',',1,2)+1) as name3
from tb;

Answer 2

-- 如果表 a中有超过两个逗号(,)，则可以这样：
with tb as (select id, name||',' as name from a)
select id, 
       substr(name,1,instr(name,',',1,1)-1) as name1,
       substr(name,instr(name,',',1,1)+1,instr(name,',',1,2)-instr(name,',',1,1)-1) as name2,
       substr(name,instr(name,',',1,2)+1,instr(name,',',1,3)-instr(name,',',1,2)-1) as name3
from tb;

Answer 3

因为，名字里面的,号个数不定，所以用过程或者函数循环取吧。先取出一共有几个逗号，
然后循环用instrb和substrb来取。

Answer 4

你的第一个查询出的结果是这样子的
id  name1  name2  name3
1   张三             张三，李四
2   张三      李四   王五
3                 李四第二种是这样 
1   张三   
2   张三  李四
3
两种都不对啊  

Answer 5

scott@TBWORA> CREATE TABLE a(id number(18,0), name varchar2(200));表已创建。scott@TBWORA>
scott@TBWORA> INSERT INTO a(id,name) values(1,'张三,李四');已创建 1 行。scott@TBWORA> INSERT INTO a(id,name) values(2,'张三,李四,王五');已创建 1 行。scott@TBWORA> INSERT INTO a(id,name) values(2,'李四');已创建 1 行。scott@TBWORA> SELECT * FROM a;        ID NAME
---------- ------------------------------
         1 张三,李四
         2 张三,李四,王五
         2 李四scott@TBWORA> with tb as (select id, name||',' as name from a)
  2  select id,
  3         substr(name,1,instr(name,',',1,1)-1) as name1,
  4         substr(name,instr(name,',',1,1)+1,instr(name,',',1,2)-instr(name,',',1,1)-1) as name2,
  5         substr(name,instr(name,',',1,2)+1,instr(name,',',1,3)-instr(name,',',1,2)-1) as name3
  6  from tb;        ID NAME1           NAME2           NAME3
---------- --------------- --------------- ---------------
         1 张三            李四
         2 张三            李四            王五
         2 李四

Answer 6

scott@TBWORA> CREATE TABLE a(id number(18,0), name varchar2(200));表已创建。scott@TBWORA>
scott@TBWORA> INSERT INTO a(id,name) values(1,'张三,李四');已创建 1 行。scott@TBWORA> INSERT INTO a(id,name) values(2,'张三,李四,王五');已创建 1 行。scott@TBWORA> INSERT INTO a(id,name) values(3,'李四');已创建 1 行。scott@TBWORA> COMMIT;提交完成。scott@TBWORA>  SELECT * FROM a;        ID NAME
---------- ------------------------------
         1 张三,李四
         2 张三,李四,王五
         3 李四scott@TBWORA> with tb as (select id, name||',' as name from a)
  2  select id,
  3         substr(name,1,instr(name,',',1,1)-1) as name1,
  4         substr(name,instr(name,',',1,1)+1,instr(name,',',1,2)-instr(name,',',1,1)-1) as name2,
  5         substr(name,instr(name,',',1,2)+1,instr(name,',',1,3)-instr(name,',',1,2)-1) as name3
  6  from tb;        ID NAME1           NAME2           NAME3
---------- --------------- --------------- ---------------
         1 张三            李四
         2 张三            李四            王五
         3 李四

Answer 7

--给个动态的procedure例子吧：--过程：
create or replace procedure row_to_col_func(cur out sys_refcursor)
as
  sqlstr varchar2(2000):='select id';
  lv number;
begin
     select max(length(name)-length(replace(name,',',''))+1) into lv from tab1 ;
     for rs in 1..lv  loop
       sqlstr:=sqlstr||chr(10)||','||'max(regexp_substr(name,''[^,]+'',1,'||rs||')) name'||rs;
     end loop ;
     sqlstr:=sqlstr||chr(10)||'from tab1  group by id ' ;
     open cur for sqlstr;
end row_to_col_func;
--测试：
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on
SQL> set linesize 30000
SQL> select * from tab1;        ID NAME
---------- ----------------------------------------
         1 张三,李四
         2 张三,李四,王五
         3 李四SQL> col name1 format a10
SQL> col name2 format a10
SQL> col name3 format a10
SQL> var cur refcursor
SQL> exec row_to_col_func(:cur);PL/SQL procedure successfully completed.SQL> print cur        ID NAME1      NAME2      NAME3
---------- ---------- ---------- ----------
         1 张三         李四
          2 张三         李四         王五
          3 李四SQL> 

Answer 8

--补充说句，前面我用了正则表达式，要求版本Oracle 10g，
--如果你不是，那么就用instr和substr来做,如下：create or replace procedure row_to_col_func(cur out sys_refcursor)
as
  sqlstr varchar2(2000):='select id';
  lv number;
begin
     select max(length(name)-length(replace(name,',',''))+1) into lv from tab1 ;
     for rs in 1..lv  loop
       sqlstr:=sqlstr||chr(10)||','||'max(substr('',''||name||'','',instr('',''||name||'','','','',1,'||rs||')+1,instr('',''||name||'','','','',1,'||rs||'+1)-instr('',''||name||'','','','',1,'||rs||')-1)) name'||rs;
     end loop ;
     sqlstr:=sqlstr||chr(10)||'from tab1  group by id ' ;
     open cur for sqlstr;
end row_to_col_func;