字符串 1,0,100,5;2,100,500,20;3,500,1000,40;
转化成如下表
1 0 100 5
2 100 500 20
3 500 1000 40
------解决方案--------------------
知道字符串的固定格式吗？
如果格式固定的话，可以通过截取字符串来解决
用截取函数substring
------解决方案--------------------
- SQL code
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 52,100,500,20 2 100 500 203,500,1000,40 3 500 1000 40
------解决方案--------------------
- SQL code
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,52,100,500,203,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;
------解决方案--------------------
观望罗老湿。
------解决方案--------------------
- SQL code
--多写了几行，写了个还算通用的[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 52 100 500 203 500 1000 404 1 101 65 101 501 216 501 1001 417 2 102 78 102 502 229 502 1002 42已选择9行。