关于字符串转行解决思路

关于字符串转行
rt，上次在各位大侠的热情帮助下解决了多行转化成字符串的问题，这次又碰到麻烦了 
  id

1,2,4,5,6,8

怎么把它转化成
id details
1 1
2 2
3 4
4 5
5 6
6 8

简单的说就是怎么把字符串拆成多行，csdn是个神奇的地方，还请各位大侠帮帮忙，指点迷津

[解决办法]
with tt as(select '1,2,4,5,6,8' a from dual)

select level,substr(a,instr(','||a,',',1,level),instr(a||',',',',1,level)-instr(','||a,',',1,level))a
from tt
connect by level<=length(a)-length(replace(a,','))+1

LEVELA
11
22
34
45
56
68
[解决办法]
多个字符串的话参考
http://topic.csdn.net/u/20091023/17/CF3F75D1-DE50-4EE1-B9BE-43E4AF3C62EF.html

rownum<=后面的数字根据需要来设，要大于字符串中逗号数的最大值
[解决办法]

探讨

with tt as(select '1,2,4,5,6,8' a from dual)

select level,substr(a,instr(','||a,',',1,level),instr(a||',',',',1,level)-instr(','||a,',',1,level))a
from tt
connect by level <=length(a)-length(replace(a,','))+1

LEVELA
11
22
34
45
56
68

[解决办法]
SQL> with tt as(select '1,2,4,5,6,8' a from dual)
2 select rownum id, substr(a,rownum*2-1,1) details from tt
3 connect by rownum<=(length(a)+1)/2
4 ;
 
ID DETAILS
---------- -------
1 1
2 2
3 4
4 5
5 6
6 8
 
6 rows selected
[解决办法]

探讨

with tt as(select '1,2,4,5,6,8' a from dual)

select level,substr(a,instr(','||a,',',1,level),instr(a||',',',',1,level)-instr(','||a,',',1,level))a
from tt
connect by level <=length(a)-length(replace(a,','))+1

LEVEL A
1 1
2 2
3 4
4 5
5 6
6 8

[解决办法]

探讨

SQL> with tt as(select '1,2,4,5,6,8' a from dual)
  2  select rownum id, substr(a,rownum*2-1,1) details from tt
  3  connect by rownum <=(length(a)+1)/2
  4  ;

        ID DETAILS
---------- -------
        1 1
        2 2
        3 4
        4 5
        5 6
        6 8

6 rows selected

[解决办法]

探讨

引用:
SQL> with tt as(select '1,2,4,5,6,8' a from dual)
  2  select rownum id, substr(a,rownum*2-1,1) details from tt
  3  connect by rownum <=(length(a)+1)/2
  4  ;

        ID DETAILS
---------- -------
        1 1
        2 2
        3 4
        4 5
        5 6
        6 8

6 rows selected
呵呵 你这个有点瑕疵
都是一位数的话是可以的 有个两位数就不行了
SQL codeSQL>with ttas(select'1,2,4,55,6,8' afrom dual)2select rownum id, substr(a,rownum*2-1,1) detailsfrom tt3 connectby rownum<=(length(a)+1)/2;

ID DET---------- ---112234455 ,6 ,6 rows selected.

------解决方案--------------------

如果不是1位数的话 就要截取分隔符的位置了 就想2楼的那样
[解决办法]

探讨

with tt as(select '1,2,4,5,6,8' a from dual)

select level,substr(a,instr(','||a,',',1,level),instr(a||',',',',1,level)-instr(','||a,',',1,level))a
from tt
connect by level <=length(a)-length(replace(a,','))+1

LEVELA
11
22
34
45
56
68

[解决办法]

探讨

with tt as(select '1,2,4,5,6,8' a from dual)

select level,substr(a,instr(','||a,',',1,level),instr(a||',',',',1,level)-instr(','||a,',',1,level))a
from tt
connect by level <=length(a)-length(replace(a,','))+1

LEVEL A
1 1
2 2
3 4
4 5
5 6
6 8

[解决办法]

探讨

- - 都没人知道么  杯具啊

[解决办法]

探讨

引用:
引用:
- - 都没人知道么  杯具啊

上面不是给你写了吗

我要的是 开始id里面的字符串个数是不确定的，可能是1到100000之间的任意N个数，ok？

[解决办法]

探讨

引用:
引用:
- - 都没人知道么  杯具啊

上面不是给你写了吗

我要的是 开始id里面的字符串个数是不确定的，可能是1到100000之间的任意N个数，ok？

[解决办法]

探讨

多个字符串的话参考
http://topic.csdn.net/u/20091023/17/CF3F75D1-DE50-4EE1-B9BE-43E4AF3C62EF.html

rownum <=后面的数字根据需要来设，要大于字符串中逗号数的最大值

[解决办法]
汗。。。楼主按楼上这样先试试吧，rownum<后面的数字注意控制一下
[解决办法]

SQL code

with tt as(select '1,2,4,5,6,8' name from dual) selectsubstr(name,b.id,instr(a.name||',',',',b.id)-b.id) as name from tt a,(select rownum as id from dual connect by rownum<=100 ) bwhere substr(','||a.name,b.id,1)=','
[解决办法]
SQL code
with tt as(select '1,2,4,5,6,8' name from dual) select row_number()over(order by b.id) as num,substr(name,b.id,instr(a.name||',',',',b.id)-b.id) as name from tt a,(select rownum as id from dual connect by rownum<=100 ) bwhere substr(','||a.name,b.id,1)=','
[解决办法]
SQL code
--任意数字都可以的啊with tt as(select '1,200,44,1115,6,8' name from dual) select row_number()over(order by b.id) as num,substr(name,b.id,instr(a.name||',',',',b.id)-b.id) as name from tt a,(select rownum as id from dual connect by rownum<=100 ) bwhere substr(','||a.name,b.id,1)=',' 

 

查看更多 下一篇