oracle中level用法简介

张兴康

level：把一个字符串按某一分隔符分隔后，变为数据列，即把字符串行变为列
示例1：有规则的字符串

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

复制代码

效果图：

示例2：无规则的字符串

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

复制代码

效果图：

张兴康

自顶一个

张兴康

万望 发表于 2016-2-25 00:06
玩的越来越高级了哈

这个不够高级啊

张兴康

陈晓龙 发表于 2016-2-26 16:17
咦，这个还没用过呢！

正好学习啊