Oracle SQL 劈开字符串

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle SQL 劈开字符串，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含1896字，纯文字阅读大概需要3分钟。

内容图文

id_,REGEXP_SUBSTR(name_, ‘[^,]+‘, 1, rownum) h2 FROM (select id_,name_ from test_reg_count t where t.id_ =2 ) CONNECT BY level <= length(name_)-length(replace(name_,‘,‘,‘‘))+1; --或者 SELECT id_,REGEXP_SUBSTR(name_, ‘[^,]+‘, 1, rownum) h2 FROM (select id_,name_ from test_reg_count t where t.id_ =2 ) CONNECT BY REGEXP_SUBSTR(name_, ‘[^,]+‘, 1, rownum) is not null;

三、劈开多行

SELECT id_,REGEXP_SUBSTR(name_, ‘[^,]+‘, 1, level) name_
  FROM  test_reg_count   
CONNECT BY level <= length(name_)-length(replace(name_,‘,‘,‘‘))+1         
       AND name_ = PRIOR name_          
       AND PRIOR dbms_random.value <1;
       
--或者
SELECT id_,REGEXP_SUBSTR(name_, ‘[^,]+‘, 1, level) name_
  FROM  test_reg_count   
CONNECT BY level <= length(name_)-length(replace(name_,‘,‘,‘‘))+1         
       AND name_ = PRIOR name_          
       AND PRIOR SYS_GUID() is not null;

这里最重要的是 prior操作符，关于prior的解释Oracle的官方文档是这么描述的：

　　In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator. If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. PRIOR evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

　　PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.

Oracle SQL 劈开字符串

标签：

本文系统来源：http://www.cnblogs.com/Alex-Zeng/p/5920691.html

内容总结

以上是互联网集市为您收集整理的Oracle SQL 劈开字符串全部内容，希望文章能够帮你解决Oracle SQL 劈开字符串所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---