1、当需要把一个字符串按某一分隔符分隔后,变为数据列,即把字符串行变为列,可以使用level关键字,例子:
with t as
(select 'a;b;c;d;e' as str from dual)
select level,
t.str,
substr(t.str, 2 * (level - 1) + 1, 1) as str_signle
from t
connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;
运行结果:
2、上面的写法只是适用于一般有规律的字符串行,当遇到不规则字符串行时,可以使用oracle的正则表达式函数,请看下面的例子:
with t as
(select 'i;am;a;test;hahahhah' as str from dual)
select level,
str,
regexp_substr(t.str, '[^;]+', 1, level) str_single
from t
connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;
运行结果:
或者不使用正则表达式:
with t_org as
(select 'I am a complicated string' as str from dual),
t_sep as
(select ' ' as sep from dual),
t as
(select t_org.str as orign_str,
t_sep.sep || t_org.str || t_sep.sep as str
from t_org,
t_sep)
select level,
t.orign_str,
/* instr(t.str, t_sep.sep, 1, level) as separator_postion,
instr(t.str, t_sep.sep, 1, level) + 1 as str_postion_begin,
instr(t.str, t_sep.sep, 1, level + 1) -
instr(t.str, t_sep.sep, 1, level) - 1 as str_single_len,*/
substr(t.str, instr(t.str, t_sep.sep, 1, level) + 1, instr(t.str, t_sep.sep, 1, level + 1) -
instr(t.str, t_sep.sep, 1, level) - 1) as str_signle
from t,
t_sep
connect by level < length(t.str) - length(replace(t.str, t_sep.sep, ''));
运行结果: