oracle 字段截取

w844638059 2011-08-19 02:23:25

表a 中有字段
id name
1 张三，李四
2 张三，李四，王五
3 李四

查询出来的结果是

id name1 name2 name3
1 张三李四
2 张三李四王五
3 李四

查询语句怎么写啊

...全文

1199 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

gelyon 2011-08-19

打赏
举报



--补充说句，前面我用了正则表达式，要求版本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;

cyousor 2011-08-19

打赏
举报

[Quote=引用 8 楼 gelyon 的回复:]

SQL code

--给个动态的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)-leng……
[/Quote]
+1

gelyon 2011-08-19

打赏
举报



--给个动态的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 - Production



SQL> 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>

luoyoumou 2011-08-19

打赏
举报

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 李四

luoyoumou 2011-08-19

打赏
举报

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 李四

w844638059 2011-08-19

打赏
举报

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

第二种是这样
1 张三
2 张三李四
3

两种都不对啊

yixilan 2011-08-19

打赏
举报

[Quote=引用楼主 w844638059 的回复:]
表a 中有字段
id name
1 张三，李四
2 张三，李四，王五
3 李四

查询出来的结果是

id name1 name2 name3
1 张三李四
2 张三李四王五
3 李四

查询语句怎么写啊
[/Quote]
因为，名字里面的,号个数不定，所以用过程或者函数循环取吧。先取出一共有几个逗号，
然后循环用instrb和substrb来取。

luoyoumou 2011-08-19

打赏
举报

-- 如果表 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;

gelyon 2011-08-19

打赏
举报

像你这种不定列的，最好用过程来做

luoyoumou 2011-08-19

打赏
举报

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;