oracle split去逗号,行列转换
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle split去逗号,行列转换,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3433字,纯文字阅读大概需要5分钟。
内容图文
![oracle split去逗号,行列转换](/upload/InfoBanner/zyjiaocheng/907/760198b7e4c8402ab5e3768681b467cf.jpg)
1.针对 '1','2','3','4','5'(逗号在字符串外面)
[sql] view plaincopyprint?- SQL> SELECT COLUMN_VALUE FROMTABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));
- COLUMN_VALUE
- --------------------------------------------------------------------------------
- 1
- 2
- 3
- 4
- 5
SQL> SELECT COLUMN_VALUE FROM TABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));
COLUMN_VALUE
--------------------------------------------------------------------------------
1
2
3
4
5
2.针对'1,2,3,4,5'(逗号在字符串里面)
[sql] view plaincopyprint?- SQL> select regexp_substr('1,2,3,4,5','[^,]+',1,rownum) from dual
- 2 connectby rownum<=length('1,2,3,4,5')-length(replace('1,2,3,4,5',','))+1
- 3 ;
- REGEXP_SUBSTR('1,2,3,4,5','[^,
- ------------------------------
- 1
- 2
- 3
- 4
- 5
SQL> select regexp_substr('1,2,3,4,5','[^,]+',1,rownum) from dual
2 connect by rownum<=length('1,2,3,4,5')-length(replace('1,2,3,4,5',','))+1
3 ;
REGEXP_SUBSTR('1,2,3,4,5','[^,
------------------------------
1
2
3
4
5
3.使用函数
[sql] view plaincopyprint?- CREATEORREPLACE TYPE ty_str_split ISTABLEOF VARCHAR2 (4000);
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);
[sql] view plaincopyprint?
- CREATEORREPLACEFUNCTION fn_split (p_str IN CLOB, p_delimiter IN VARCHAR2)
- RETURN ty_str_split
- IS
- j INT := 0;
- i INT := 1;
- len INT := 0;
- len1 INT := 0;
- str VARCHAR2 (4000);
- str_split ty_str_split := ty_str_split ();
- BEGIN
- len := LENGTH (p_str);
- len1 := LENGTH (p_delimiter);
- WHILE j < len
- LOOP
- j := INSTR (p_str, p_delimiter, i);
- IF j = 0
- THEN
- j := len;
- str := SUBSTR (p_str, i);
- str_split.EXTEND;
- str_split (str_split.COUNT) := str;
- IF i >= len
- THEN
- EXIT;
- END IF;
- ELSE
- str := SUBSTR (p_str, i, j - i);
- i := j + len1;
- str_split.EXTEND;
- str_split (str_split.COUNT) := str;
- END IF;
- END LOOP;
- RETURN str_split;
- END fn_split;
CREATE OR REPLACE FUNCTION fn_split (p_str IN CLOB, p_delimiter IN VARCHAR2)
RETURN ty_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
str_split ty_str_split := ty_str_split ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
str_split.EXTEND;
str_split (str_split.COUNT) := str;
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
str_split.EXTEND;
str_split (str_split.COUNT) := str;
END IF;
END LOOP;
RETURN str_split;
END fn_split;
测试:
[sql] view plaincopyprint?- <p>SQL> select * fromtable(fn_split('1,2,3,4,5',',')); --第二个单引号中是前面字符串中需要被分隔的字符</p><p>COLUMN_VALUE
- --------------------------------------------------------------------------------
- 1
- 2
- 3
- 4
- 5</p><p>SQL> select * fromtable(fn_split('1,2,3,4。5','。'));</p><p>COLUMN_VALUE
- --------------------------------------------------------------------------------
- 1,2,3,4
- 5</p><p>SQL></p>
SQL> select * from table(fn_split('1,2,3,4,5',',')); --第二个单引号中是前面字符串中需要被分隔的字符
COLUMN_VALUE -------------------------------------------------------------------------------- 1 2 3 4 5
SQL> select * from table(fn_split('1,2,3,4。5','。'));
COLUMN_VALUE -------------------------------------------------------------------------------- 1,2,3,4 5
SQL>
转载于:https://www.cnblogs.com/zhichao-116/p/3645609.html
内容总结
以上是互联网集市为您收集整理的oracle split去逗号,行列转换全部内容,希望文章能够帮你解决oracle split去逗号,行列转换所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。