oracle 函数问题

Question

各位大哥:
我有一个字符串'sadf,es2sadfs,dd8,1ds2,16dd,19d,23ds'
我想把
sadf
es2sadfs
dd8
1ds2
16dd
19d
23ds
都取出来, 怎么取啊 ?  oracle有没有内部函数?

Answer 1

oracle行列转换总结

Answer 2

with tb as(
select 'sadf,es2sadfs,dd8,1ds2,16dd,19d,23ds' id from dual)
select
substr(','||id||',',instr(','||id||',',',',1,rownum)+1,
        instr(','||id||',',',',1,rownum+1)-instr(','||id||',',',',1,rownum)-1) newid,
instr(','||id||',',',',1,rownum)col1,  --','rownum次出现的位置
instr(','||id||',',',',1,rownum+1)col2, --','rownum+1次出现的位置
instr(','||id||',',',',1,rownum+1)-instr(','||id||',',',',1,rownum) col3 --要截取的长度
from tb
connect by rownum <= length(','||id||',')
     - length(replace(','||id||',', ',', ''))-1NEWID                                                                              COL1       COL2       COL3
---------------------------------------------------------------------------- ---------- ---------- -
sadf                                                                                  1          6          5
es2sadfs                                                                              6         15          9
dd8                                                                                  15         19          4
1ds2                                                                                 19         24          5
16dd                                                                                 24         29          5
19d                                                                                  29         33          4
23ds                                                                                 33         38          5

Answer 3

select regexp_substr('sadf,es2sadfs,dd8,1ds2,16dd,19d,23ds','[^,]+',1,level) as cl1
from dual 
connect by level<=length('sadf,es2sadfs,dd8,1ds2,16dd,19d,23ds')-length(replace('sadf,es2sadfs,dd8,1ds2,16dd,19d,23ds',',',''))+1;

Answer 4

select regexp_replace('sadf,es2sadfs,dd8,1ds2,16dd,19d,23ds',',',''||chr(10)||'') from dual; 搜索出来的结果copy到你的文本文件中就是你要的结果了

Answer 5

4楼的挺厉害的SQL> select regexp_substr('sadf,es2sadfs,dd8,1ds2,16dd,19d,23ds','[^,]+',1,level) as cl1
  2  from dual
  3  connect by level<=length('sadf,es2sadfs,dd8,1ds2,16dd,19d,23ds')-
  4          length(replace('sadf,es2sadfs,dd8,1ds2,16dd,19d,23ds',',',''))+1;
/*
CL1
------------------------------------------------------------------------
sadf
es2sadfs
dd8
1ds2
16dd
19d
23ds7 rows selected
*/

Answer 6

select regexp_replace('sadf,es2sadfs,dd8,1ds2,16dd,19d,23ds',',',''||chr(10)||'') from dual;