如何将一个字符串转化成一个表？

Question

字符串  1,0,100,5;2,100,500,20;3,500,1000,40;转化成如下表
1 0 100 5
2 100 500 20
3 500 1000 40

Answer 1

知道字符串的固定格式吗？如果格式固定的话，可以通过截取字符串来解决用截取函数substring

Answer 2

sys@TBWORA> with a as (select ';'||'1,0,100,5;2,100,500,20;3,500,1000,40;' as str1 from dual),
  2       b as (select substr(str1, instr(str1,';',1,level)+1,
  3                                 instr(str1,';',1,level+1)-instr(str1,';',1,level)-1) as str2
  4              from a connect by level <=length(a.str1)-length(replace(a.str1,';','')) ),
  5       c as (select str2,
  6                    substr(str2,1, instr(str2,',',1,1)-1) as str2_p1,
  7                    substr(str2,instr(str2,',',1,1)+1,instr(str2,',',1,2)-instr(str2,',',1,1)-1) as str2_p2,
  8                    substr(str2,instr(str2,',',1,2)+1,instr(str2,',',1,3)-instr(str2,',',1,2)-1) as str2_p3,
  9                    substr(str2,instr(str2,',',1,3)+1) as str2_p4
 10             from b )
 11  select c.str2, c.str2_p1, c.str2_p2, c.str2_p3, c.str2_p4 from c;STR2                STR2_P1      STR2_P2      STR2_P3      STR2_P4
------------------- ------------ ------------ ------------ ------------
1,0,100,5           1            0            100          5
2,100,500,20        2            100          500          20
3,500,1000,40       3            500          1000         40

Answer 3

with a as (select ';'||'1,0,100,5;2,100,500,20;3,500,1000,40;' as str1 from dual),
     b as (select substr(str1, instr(str1,';',1,level)+1,
                               instr(str1,';',1,level+1)-instr(str1,';',1,level)-1) as str2 
            from a connect by level <=length(a.str1)-length(replace(a.str1,';','')) )
select b.str2 from b;STR2
------------------------------------------------------------
1,0,100,5
2,100,500,20
3,500,1000,40
-- 上一步是拆分分号(;)，下一步是拆分逗号(,) (如果你每部分的逗号最多三个，即：最多四个字段，则可以这样写：)
with a as (select ';'||'1,0,100,5;2,100,500,20;3,500,1000,40;' as str1 from dual),
     b as (select substr(str1, instr(str1,';',1,level)+1,
                               instr(str1,';',1,level+1)-instr(str1,';',1,level)-1) as str2 
            from a connect by level <=length(a.str1)-length(replace(a.str1,';','')) ),
     c as (select str2, 
                  substr(str2,1, instr(str2,',',1,1)-1) as str2_p1,
                  substr(str2,instr(str2,',',1,1)+1,instr(str2,',',1,2)-instr(str2,',',1,1)-1) as str2_p2,
                  substr(str2,instr(str2,',',1,2)+1,instr(str2,',',1,3)-instr(str2,',',1,2)-1) as str2_p3,
                  substr(str2,instr(str2,',',1,3)+1) as str2_p4
           from b )
select c.str2, c.str2_p1, c.str2_p2, c.str2_p3, c.str2_p4 from c;

Answer 4

--多写了几行，写了个还算通用的
[SYS@orcl] SQL>WITH t AS(
  2    SELECT '1,0,100,5;2,100,500,20;3,500,1000,40;' col FROM dual
  3    UNION ALL
  4    SELECT '4,1,101,6;5,101,501,21;6,501,1001,41;' col FROM dual
  5    UNION ALL
  6    SELECT '7,2,102,7;8,102,502,22;9,502,1002,42;' col FROM dual
  7  )--上面是模拟数据，可以不用管
  8  SELECT REGEXP_SUBSTR(A, '[^, ]+', 1, 1) AS A1, --取第一个逗号前面所有字符(如果变换为其他字符自己改正)
  9         REGEXP_SUBSTR(A, '[^, ]+', 1, 2) AS A2, --取第二个逗号前面所有字符(如果变换为其他字符自己改正)
 10         REGEXP_SUBSTR(A, '[^, ]+', 1, 3) AS A3, --取第三个逗号前面所有字符(如果变换为其他字符自己改正)
 11         REGEXP_SUBSTR(A, '[^, ]+', 1, 4) AS A4  --取第四个逗号前面所有字符(如果变换为其他字符自己改正，如果还有字段继续按照规律添加【1,5】之类的)
 12    FROM (SELECT REGEXP_SUBSTR(RTRIM(COL, ';'), '[^; ]+', 1, L) AS A
 13            FROM T, (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100) T1
 14           WHERE LENGTH(RTRIM(COL, ';')) -
 15                 LENGTH(REPLACE(RTRIM(COL, ';'), ';')) + 1 >= T1.L) --子查询为按照[;]分隔你的字符串，最大支持100-1=99个[;]（不包括字符串最后的分号），想支持更大将[<=100]自行调整
 16    ORDER BY A1
 17  ;A1    A2    A3    A4
----- ----- ----- -----
1     0     100   5
2     100   500   20
3     500   1000  40
4     1     101   6
5     101   501   21
6     501   1001  41
7     2     102   7
8     102   502   22
9     502   1002  42已选择9行。

Answer 5

2,100,500,20;  这里如果是2,,,20; 结果就是
A1    A2    A3    A4
----- ----- ----- -----
1     0     100   5
2     20我想要的是
A1    A2    A3    A4
----- ----- ----- -----
1     0     100   5
2     0     0     20
该改哪里？

Answer 6

使用5楼SQL之前处理一下你的字符串，把里面的,,替换成,0,就可以了。    FROM (SELECT Replace(REGEXP_SUBSTR(RTRIM(COL, ';'), '[^; ]+', 1, L),',,',',0,') AS A