oracle根据特定字符拆分字符串的⽅法
清洗数据需要将某个字段内以空格分隔的字符串拆分成多⾏单个的字符串，百度了很多种⽅法⼤概归结起来也就这⼏种⽅法最为有效，现在把贴出来：
第⼀种：
select regexp_substr('1 2 3','[^ ]+',1,level,'i') from dual
connect by level <= length('1 2 3') -length(regexp_replace('1 2 3',' ',''))+1;
效果就是这个样⼦↓
效果
这种⽅法的核⼼就是regexp_substr函数，通过正则来拆分字符串，函数⽤法为：
regexp_substr(str，进⾏匹配的正则表达式,position，标志第⼏个匹配组，modifier)。
该函数的⼀个缺点就是只能每次取⼀个字符串出来，这个就⽐较头痛了，因为现在我要取分割后所有的字符串，所以现在就要⽤到connect by命令，来限制取⼏个匹配组，当然我们是要全部的，所以就通过lengt
h来实时确定所取得匹配组数量。
通过这种⽅法就可以实现开题的需求，但在⽤的过程中发现⼀个问题，如果说我要给拆分后的字符串带上ID的话这种⽅法就貌似失灵了，会⽆限次取，所以问题没解决，有⼈知道的话⿇烦可以告诉我⼀下。
第⼆种
1create or replace function split(p_list varchar,p_sep varchar :='')两个参数，⼀个实传⼊字符串名，第⼆个是根据什么来分割
2
3return type_split pipelined
4
5IS
6
7 l_idx pls_integer;
8
9 v_list varchar2(50) := p_list;
10
11begin
12
13  loop
14
15    l_idx :=instr(v_list,p_sep);
oracle 字符串转数组16
17if l_idx = 0then
18
19      piperow(substr(v_list,1,l_idx-1));
20
21      v_list :=substr(v_list,l_idx+length(p_sep));
22
23else
24
25      piperow(v_list);
26
27exit;
28
29end if;
30
31end loop;
32
33end split;
通过创建函数的⽅法实现拆分字符串，缺点同⼀，⽆法实现取ID
⽤法：
selelct *from table(split(这⾥写字符串,’ ’));
与第⼆种异曲同⼯的还有接下来这⼀种
第⼆种.2
1CREATE OR REPLACE
2FUNCTION splitstr (str IN CLOB,
3                                        i  IN NUMBER :=0,
4                                        sep IN VARCHAR2 :=','
5  )
6RETURN VARCHAR2
7/**************************************
852  * Name:        splitstr
953  * Author:      Sean Zhang.
1054  * Date:        2012-09-03.
1155  * Function:    返回字符串被指定字符分割后的指定节点字符串。
1256  * Parameters:  str: 待分割的字符串。
1357                i: 返回第⼏个节点。当i为0返回str中的所有字符，当i 超过可被分割的个数时返回空。
1458                sep: 分隔符，默认逗号，也可以指定字符或字符串。当指定的分隔符不存在于str中时返回sep中的字符。
1559  * Example:    select splitstr('abc,def', 1) as str from dual;  得到 abc
1660                select splitstr('abc,def', 3) as str from dual;  得到空
1761  **************************************/
18IS
19 t_i      NUMBER;
20 t_count  NUMBER;
21 t_str    VARCHAR2 (4000);
22BEGIN
23IF i =0
24THEN
25 t_str  :=str;
26 ELSIF INSTR (str, sep) =0
27THEN
28 t_str  := sep;
29ELSE
30SELECT COUNT ( * )
31INTO t_count
32FROM table (split (str, sep));
33IF i <= t_count
34THEN
35SELECT str
36INTO t_str
37FROM (SELECT ROWNUM AS item, COLUMN_VALUE AS str
38FROM table (split (str, sep)))
39WHERE item = i;
40END IF;
41END IF;
42
43RETURN t_str;
44END;
第三种：
个⼈认为第三种才是最有效果的⽤了with as命令，以下优点解释摘⾃⽹络
With查询语句不是以select开始的，⽽是以“WITH”关键字开头可认为在真正进⾏查询之前预先构造了⼀个临时表TT，之后便可多次使⽤它做进⼀步的分析和处理
WITHClause⽅法的优点：增加了SQL的易读性，如果构造了多个⼦查询，结构会更清晰；更重要的是：“⼀次分析，多次使⽤”，这也是为什么会提供性能的地⽅，达到了“少读”的⽬标。
第⼀种使⽤⼦查询的⽅法表被扫描了两次，⽽使⽤WITH Clause⽅法，表仅被扫描⼀次。这样可以⼤⼤的提⾼数据分析和查询的效率。
另外，观察WITH Clause⽅法执⾏计划，其中“SYS_TEMP_XXXX”便是在运⾏过程中构造的中间统计结果临时表。
1with temp0 as (select LEVEL lv from dualCONNECT BY LEVEL<=100)
2
3select id,
4
5 substr(t.vals,instr(t.vals, '', 1, tv.lv)+1,instr(t.vals, '', 1, tv.lv +1)-(instr(t.vals, '', 1, tv.lv) +1)) ASattr4
6
7from
8
9 (select id,''|| attr4 ||'' ASvals,length(attr4 ||'') - nvl(length(REPLACE(attr4, '')), 0) AS cnt
10
11  fromT_FLIGHT_TLX_TOTAL where rownum <3) t
12
13join temp0 tv  on  tv.lv <= tt  order by1;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。