【精选】Oracle根据逗号拆分字段内容转成多行_oracle按逗号拆分列为多行_EstherLty的博客

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
1.业务表A

SELECT * FROM app_template_dept t WHERE t.evaluate_index_code ='3330326';
2.实现拆分SQL
 
SELECT DISTINCT A.*, REGEXP_SUBSTR(A.DEPT_CODE, '[^,]+', 1, Level, 'i')
  FROM (SELECT *
          FROM APP_TEMPLATE_DEPT T
         WHERE T.EVALUATE_INDEX_CODE = '3330326') A
CONNECT BY Level<= LENGTH(A.DEPT_CODE) -
           LENGTH(REGEXP_REPLACE(A.DEPT_CODE, ',', '')) + 1;
3.REGEXP_SUBSTR函数
 
对字符串进行正则分割，取特定字符的函数。 
1）参数说明
 
REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) 
 String: 需要进行正则处理的字符串
 
 pattern：进行匹配的正则表达式
 
 position：起始位置，从第几个字符开始正则表达式匹配（默认为1）
 注意：字符串最初的位置是1而不是0
 
 occurrence：标识第几个匹配组，默认为1
 注意：分割后最初的字符串会按分割的顺序排列成组
 
 modifier：模式（'i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’。）
 注意：针对的是正则表达式里字符大小写的匹配
  
/*从第一个字符开始按照字符'A'(区分大小写)分割字符串'1a2A33a'，取第二个分割出来的数据*/
SELECT REGEXP_SUBSTR('1a2A33a','[^A]+',1,2,'c') AS STR FROM DUAL; --结果：33a
SELECT REGEXP_SUBSTR('1a2A33a','[^A]+',1,2,'i') AS STR FROM DUAL; --结果：2
/*把要输出来的第几个子串，通过一个变量ROWNUM转换成输出多少个子串。level<=5代表的是输出5个，没有的为null*/
SELECT REGEXP_SUBSTR('1a2A33a','[^A]+',1,Level,'i') AS STR FROM DUAL CONNECT BY LEVEL<=5; --结果：1、2、33、null、null
4.REGEXP_REPLACE函数
 
通过正则表达式来进行匹配替换。 
1）参数说明
 
REGEXP_REPLACE(VARCHAR str, VARCHAR pattern, VARCHAR replacement) 
str：指定的字符串
pattern：被替换的字符串
replacement：用于替换的字符串 
SELECT REGEXP_REPLACE('1a2A33a','A','') AS STR FROM DUAL; --结果：1a233a
5.CONNECT BY函数
 
一般用来查找存在父子关系的数据，也就是树形结构的数据。 
1）基本语法
 
select ... from
where ... --过滤条件，用于对返回的所有记录进行过滤。
[start with ...] --查询结果重起始根结点的限定条件。
connect by [prior] id=parentid--连接条件
 start with：用来限制第一层的数据，或者叫根节点数据,以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。
 
 connect by [prior] id=parentid ：这部分是用来指明oracle在查找数据时以怎样的一种关系去查找，比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。
  
也可以写成connect by id= [prior] parentid，这种用法就表示从下往上查找数据，可以理解为从叶子节点往上查找父级几点，用第一层数据的parentid去跟表记录里面的id进行匹配，匹配成功那么查找出来的就是第二层数据；上面的那种就是从父级节点往下查找叶子节点。 
select rownum from dual connect by rownum<=10;
select * from table_temp
start with id = '001001'
connect by prior id = parent_id;
table_temp表中数据
  
6.LENGTH函数
 
返回字符串表达式中字符数的字符串函数。 
1）参数说明
 
LENGTH(string-expression) 
string-expression：字符串表达式，可以是列名、字符串文字或另一个标量函数的结果，其中基础数据类型可以表示为任何字符类型（例如 CHAR 或 VARCHAR） 
SELECT LENGTH('1a2A33a') AS STR FROM DUAL; --结果：7
REGEXP_SUBSTR函数格式如下：
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr     ：需要进行正则处理的字符串
__pattern    ：进行匹配的正则表达式
__position   ：起始位置，从第几个字符开始正则表达式匹配（默认为
SELECT DISTINCT T.NAME,
       REPLACE(REGEXP_SUBSTR(T.TESTSTR, '[^,]+', 1, LEVEL), ',', ' ') TESTSTR
FROM TEST2 T
WHERE 1 = 1
      CONNECT BY LEVEL <= (LENGTH(T.TESTSTR) - LENGTH(REPLACE(T.TESTSTR, ',', '')) + 1);
-- Create table TEST2
create
select *　from (   
select enbid, REGEXP_SUBSTR(v_list, '[^,]+', 1, lv) v_value
 from (select 736021 as enbid, '5,51' as v_list from dual
    union all
    select 186168 as enbid, ...
                                    近期在工作中遇到某表某字段是可扩展数据内容，信息以逗号分隔生成的，现需求要根据此字段数据在其它表查询相关的内容展现出来，第一想法是切割数据，以逗号作为切割符，以下为总结的实现方法，以供大家参考、指教。关于Oracle以逗号分隔的字符串拆分为多行数据的文章就介绍至此，更多相关Oracle拆分多行数据内容请搜索编程教程以前的文章，希望大家多多支持编程教程！
                                    这段代码会在表A中选择B和C两个字段，并使用 DISTINCT 关键字去除重复的C值。然后使用 ROW_NUMBER 函数为每个B对应的C值进行编号，最后使用 LISTAGG 函数将每个B对应的前100个C值用逗号拼接起来，形成一个新的C列。这段代码会将表A中每个B对应的多个C值用逗号拼接起来，形成一个新的C列。该代码实现将字段B根据逗号分为多行。
2.1REGEXP_SUBSTR()
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
 string:需要进行正则处理的字符串
 pattern：进行匹配的正则表达式
 position：起始位置，从字符串的第几个字符开始正则表达式匹配（默认为1） 注意：字符串最初的位置是1而不是0
 occu......
                                    工作中遇到一个问题，表A中有一个字段，存的是编码值，但是不规范，存的多个值，用逗号（既有英文逗号，又有中文逗号，还有空格）隔开了；现需要根据编码值关联对应的编码表查出编码名称，所以网上查找资料得出了解决方法。本人用的是Oracle数据库。
参考MySQL数据库解决方法：https://blog.csdn.net/myme95/article/details/82661661
一、创建测...
解决办法:
select distinct * from (
select regexp_substr(q.nums, '[^,]+', 1, Level,'i') order_num, names
  from (
  select '1,2,3' nums, '张三' names from dual
  uni...