Oracle根据指定符号将记录行拆分

女爷i 2022-12-17 08:52 162阅读 0赞

> 最近碰上一个需求，需要在数据库中将数据做二次处理。需要根据某个特定的符号，将记录行拆分。

**样例如下：分隔符是逗号**

<table> 
 <thead> 
  <tr> 
   <th>IMAGE_URL</th> 
   <th>TYPE</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>/image/01/992.jpg,/image/01/993.jpg,/image/01/994.jpg</td> 
   <td>picture</td> 
  </tr> 
 </tbody> 
</table>

**需要实现的效果如下：**

<table> 
 <thead> 
  <tr> 
   <th>IMAGE_URL</th> 
   <th>TYPE</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>/image/01/992.jpg</td> 
   <td>picture</td> 
  </tr> 
  <tr> 
   <td>/image/01/993.jpg</td> 
   <td>picture</td> 
  </tr> 
  <tr> 
   <td>/image/01/994.jpg</td> 
   <td>picture</td> 
  </tr> 
 </tbody> 
</table>

这和使用unpivot做列转行的做法还不太一样，前者是有多个列， 将列转为行。  
本例是只有一列，根据该列的某个固定分隔符，将改列转变为多行，准确说，是根据分隔符做分割行的操作。

**基本思路：**

使用正则匹配字段中的`,`，再结合connect by子句处理所有的具有父子级结构的数据行。  
connect by子句用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询。详细描述该语法比较繁多，本文不再描述。可自行查阅资料。

**sql处理的代码：**

select distinct regexp_substr(q.IMAGE_URL, '[^,]+', 1, Level, 'i') IMAGE_URL, 
                    TYPE
      from (SELECT IMAGE_URL,
      				TYPE
              FROM TABLE_1 T  
             WHERE T.TYPE = 'picture'
              ) q
     where IMAGE_URL is not null
    connect by Level <= LENGTH(q.IMAGE_URL) -
               LENGTH(REGEXP_REPLACE(q.IMAGE_URL, ',', '')) + 1
     order by IMAGE_URL;