select id,regexp_substr(row,'[^;]+',1,n) row_ from table, (select level n from dual connect by level<= (select max(length(row)-length(replace(row,',')))+1 from table )num where regexp_substr(row,'[^;]+',1,n) is not null
with tab as (select '1' as id,'111;222;333;' as value from dual union select '2' as id,'RRR;TTT;' as value from dual union select '1' as id,'444;' as value from dual) select * from (select tab.id, substr(tab.value, 1, instr(tab.value, ';', 1, 1) - 1) as value from tab union select tab.id, substr(tab.value, instr(tab.value, ';', 1, 1) + 1, instr(tab.value, ';', 1, 2) - instr(tab.value, ';', 1, 1) - 1) as value from tab union select tab.id, substr(tab.value, instr(tab.value, ';', 1, 2) + 1, instr(tab.value, ';', 1, 3) - instr(tab.value, ';', 1, 2) - 1) as value from tab) where value is not null 这样写起来很麻烦 个人感觉用正则表达式更好 但是我不会正则
SQL> with tab as (select '1' as id,'111;222;333;' as val from dual union 2 select '2' as id,'RRR;TTT;' as val from dual union 3 select '1' as id,'444;' as val from dual) 4 select id,regexp_substr(val,'[^;]+',1,n) new_val 5 from tab, 6 ( 7 select level n 8 from dual connect by level<= 9 (select max(length(val)-length(replace(val,';')))+1 from tab ) 10 ) 11 where regexp_substr(val,'[^;]+',1,n) is not null;ID NEW_VAL --- -------------------------------------------------------------------------- 1 111 1 444 2 RRR 1 222 2 TTT 1 3336 rows selected.
(select level n from dual connect by level<=
(select max(length(row)-length(replace(row,',')))+1 from table )num
where regexp_substr(row,'[^;]+',1,n) is not null
select '2' as id,'RRR;TTT;' as value from dual union
select '1' as id,'444;' as value from dual)
select *
from (select tab.id,
substr(tab.value, 1, instr(tab.value, ';', 1, 1) - 1) as value
from tab
union
select tab.id,
substr(tab.value,
instr(tab.value, ';', 1, 1) + 1,
instr(tab.value, ';', 1, 2) -
instr(tab.value, ';', 1, 1) - 1) as value
from tab
union
select tab.id,
substr(tab.value,
instr(tab.value, ';', 1, 2) + 1,
instr(tab.value, ';', 1, 3) -
instr(tab.value, ';', 1, 2) - 1) as value
from tab)
where value is not null
这样写起来很麻烦 个人感觉用正则表达式更好
但是我不会正则
LVHKC20130115001;LVHKC20130115002;
NNN MMM
1 LVHKC20130115001;LVHKC20130115002;LVHKC20130115003;LVHKC20130115004;
1 LVHKC20130115005;
2 LVHKC20130115001;
2 LVHKC20130115002;
如何转变?
我这种方法是笨方法 就是先把分号的最大个数拿出来 然后以此为依据
这样就可以处理所有分号个数比这个值小的问题,不过就是sql写的很长
2 select '2' as id,'RRR;TTT;' as val from dual union
3 select '1' as id,'444;' as val from dual)
4 select id,regexp_substr(val,'[^;]+',1,n) new_val
5 from tab,
6 (
7 select level n
8 from dual connect by level<=
9 (select max(length(val)-length(replace(val,';')))+1 from tab )
10 )
11 where regexp_substr(val,'[^;]+',1,n) is not null;ID NEW_VAL
--- --------------------------------------------------------------------------
1 111
1 444
2 RRR
1 222
2 TTT
1 3336 rows selected.