oracle pl/sql level妙用

1、当需要把一个字符串按某一分隔符分隔后，变为数据列，即把字符串行变为列，可以使用level关键字,例子：

 

[sql] view plain copy

1. with t as  
2.  (select 'a;b;c;d;e' as str from dual)  
3. select level,  
4.        t.str,  
5.        substr(t.str, 2 * (level - 1) + 1, 1) as str_signle  
6.   from t  
7. connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;  

 

运行结果：

 

2、上面的写法只是适用于一般有规律的字符串行，当遇到不规则字符串行时，可以使用oracle的正则表达式函数，请看下面的例子：

 

[sql] view plain copy

1. with t as  
2.  (select 'i;am;a;test;hahahhah' as str from dual)  
3. select level,  
4.        str,  
5.        regexp_substr(t.str, '[^;]+', 1, level) str_single  
6.   from t  
7. connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;  

 

运行结果：

或者不使用正则表达式：

 

[sql] view plain copy

1. with t_org as  
2.  (select 'I am a complicated string' as str from dual),  
3. t_sep as  
4.  (select ' ' as sep from dual),  
5. t as  
6.  (select t_org.str as orign_str,  
7.          t_sep.sep || t_org.str || t_sep.sep as str  
8.     from t_org,  
9.          t_sep)  
10. select level,  
11.        t.orign_str,  
12. /*     instr(t.str, t_sep.sep, 1, level) as separator_postion,  
13.        instr(t.str, t_sep.sep, 1, level) + 1 as str_postion_begin,  
14.        instr(t.str, t_sep.sep, 1, level + 1) -  
15.        instr(t.str, t_sep.sep, 1, level) - 1 as str_single_len,*/  
16.        substr(t.str, instr(t.str, t_sep.sep, 1, level) + 1, instr(t.str, t_sep.sep, 1, level + 1) -  
17.                instr(t.str, t_sep.sep, 1, level) - 1) as str_signle  
18.   from t,  
19.        t_sep  
20. connect by level < length(t.str) - length(replace(t.str, t_sep.sep, ''));  

 

运行结果：