目前有一张源表,里面有几千条类似这样的数据
"5-4",21,benben,"[ a, 509, 0, 29 ]"
"5-9",20,birdegg,"[ c,7,5,3,2,a, 509, 0, 29 ]"
"5-n",20,birdegg,"[ 1,2,3,4,5,6....n]"5-后面的数字不固定,n为结果表的最大列数,不知道能否实现如下功能
drop table temp
create table temp (a1 varchar(100),a2 varchar(100),a3 varchar(100),a4 varchar(100),a5 varchar(100),a6 varchar(100),a7 varchar(100),a8 varchar(100),a9 varchar(100),a10 varchar(100),a11 varchar(100),a12 varchar(100))
insert into temp
select '5-4',21,'benben','a', 509, 0, 29,null,null,null,null,null from dual
insert into temp
select '5-9',20,'birdegg','c',7,5,3,2,'a', 509, 0, 29 from dual因为不确定n有多少行,如果多一列,建表语句就要多建一行。 首先要找到字符串里面最大列数,然后对后面的影号里进行分割成列,用NULL补足插入结果表TEMP~不知道自定义函数能否实现。。急。。
select '5-4',21,'benben','a', 509, 0, 29,null,null,null,null,null from dual
和
select '5-9',20,'birdegg','c',7,5,3,2,'a', 509, 0, 29 from dual
这2个结果出来可以不。
WITH t AS
(
SELECT '"5-4",21,benben,"[ a, 509, 0, 29 ]"' AS M FROM DUAL UNION ALL
SELECT '"5-9",20,birdegg,"[ c,7,5,3,2,a, 509, 0, 29 ]"' FROM DUAL UNION ALL
SELECT '"5-n",20,birdegg,"[ 1,2,3,4,5,6,7,8,9]"' FROM DUAL
)
SELECT MAX(length(m) - length(REPLACE(m,',',''))+1) colNum
FROM
(
SELECT TRANSLATE(M,'-"[]','-') m FROM T
)--行转列,这个最好是通过PL/SQL,动态搞一些列出来
WITH A AS
(
SELECT '"5-4",21,benben,"[ a, 509, 0, 29 ]"' AS M FROM DUAL UNION ALL
SELECT '"5-9",20,birdegg,"[ c,7,5,3,2,a, 509, 0, 29 ]"' FROM DUAL UNION ALL
SELECT '"5-n",20,birdegg,"[ 1,2,3,4,5,6,7,8,9]"' FROM DUAL
)
SELECT regexp_substr(T.M,'[^(,)]+',1,1) AS col1,
regexp_substr(T.M,'[^(,)]+',1,2) AS col2,regexp_substr(T.M,'[^(,)]+',1,3) AS col3,regexp_substr(T.M,'[^(,)]+',1,4) AS col4,
regexp_substr(T.M,'[^(,)]+',1,5) AS col5,regexp_substr(T.M,'[^(,)]+',1,6) AS col6,regexp_substr(T.M,'[^(,)]+',1,7) AS col7,
regexp_substr(T.M,'[^(,)]+',1,8) AS col8,regexp_substr(T.M,'[^(,)]+',1,9) AS col9,regexp_substr(T.M,'[^(,)]+',1,10) AS col10,
regexp_substr(T.M,'[^(,)]+',1,11) AS col11,regexp_substr(T.M,'[^(,)]+',1,12) AS col12
FROM
(SELECT TRANSLATE(M,'-"[]','-') m FROM A)T
create table t(a varchar2(100));
insert into t (A)
values ('"5-4",21,benben,"[a,509,0,29]"');insert into t (A)
values ('"5-9",20,birdegg,"[c,7,5,3,2,a,509,0,29]"');insert into t (A)
values ('"5-12",19,birdkk,"[c1,7a,5,3,2,a,5091,40,229]"');
commit;
declare
max_col number := -1;
v_count number;
v_str varchar2(100);
begin
select max(to_number(regexp_replace(t.a, '.*-(\d+)".*', '\1')))
into max_col
from t;
for i in (select a,
regexp_replace(t.a, '"|\[|]', '') c,
regexp_replace(t.a, '.*\[(.*)].*', '\1') d
from t) loop
v_count := max_col - regexp_count(i.d, ',') - 1;
for j in (select regexp_substr(i.c, '+[^,$]+', 1, LEVEL) str
from dual
connect by level <= regexp_count(i.c, ',') + 1) loop
begin
v_str := v_str || to_number(j.str) || ',';
exception
when others then
v_str := v_str || '''' || j.str || ''',';
end;
end loop;
if (v_count >= 1) then
for k in 1 .. v_count loop
if (k <> v_count) then
v_str := v_str || 'null,';
else
v_str := v_str || 'null';
end if;
end loop;
end if;
dbms_output.put_line('select ' ||v_str|| ' from dual ');
v_str := '';
end loop;
end;
declare v_col int;
v_sql varchar2(1000);
v_var varchar2(1000):='[ c,7,5,3,2,a, 509, 0, 29 ]'; --以此列为例,建表
begin
v_col:=length(v_var)-length(translate(v_var,'1,','1'))+1+3; --要建立表的最大列数
dbms_output.put_line('v_col='||v_col);
v_sql:='create table test_create(';
for i in 1..v_col-1 loop
v_sql:=v_sql||'a'||i||' varchar2(100),';
end loop;
v_sql:=v_sql||'a'||v_col||' varchar2(100))';
--dbms_output.put_line('v_sql='||v_sql);
execute immediate v_sql;
end;
你们都是看ORACLE 文档学习语法的吗
你们都是看ORACLE 文档学习语法的吗边解决问题,边学。。oracle官方文档也会看。其实比较好的,还是google一把,能查出很多专题文章出来,看看就学的差不多了
你们都是看ORACLE 文档学习语法的吗边解决问题,边学。。oracle官方文档也会看。其实比较好的,还是google一把,能查出很多专题文章出来,看看就学的差不多了谢谢,看了一下午正则表达式。。还是没搞懂regexp_replace(t.a, '.*-(\d)".*', '\1') 里面\d 和 \1代表啥意思..
'.' 匹配除换行符 n之外的任何单字符。
'*' 匹配前面的子表达式零次或多次
前面我都知道了,\d代表数字?