oracle pl/sql level妙用

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 tconnect 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 tconnect 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 strfrom 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_sepconnect by level < length(t.str) - length(replace(t.str, t_sep.sep, ''));

 

运行结果：