sql:用分隔符分割字段,并转为多行

最新推荐文章于 2024-07-18 17:04:58 发布

zeeeitch

最新推荐文章于 2024-07-18 17:04:58 发布

阅读量1w

点赞数 2

分类专栏： database 文章标签： sql table

本文链接：https://blog.csdn.net/zeeeitch/article/details/7038871

版权

database 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

select * from ttt;

+--------------+
| str          |
+--------------+
| 22,333,aaa   |
| 22,ddd,aaadd |
+--------------+
2 rows in set

目标:

+-----------------------+
| 22                    |
| 333                   |
| aaa                   |
| 22                    |
| ddd                   |
| aaadd                 |
+-----------------------+

方法1 :

postgres有这个函数regexp_split_to_table

select regexp_split_to_table(str, E',') from ttt;
+-----------------------+
| regexp_split_to_table |
+-----------------------+
| 22                    |
| 333                   |
| aaa                   |
| 22                    |
| ddd                   |
| aaadd                 |
+-----------------------+
6 rows in set

方法2:

select arr[n] from 
(
    select regexp_split_to_array(str, E',') as arr,
       n as n
    from 
       ttt, 
      (SELECT generate_series(1, 100) AS n FROM dual) n 
    WHERE (n.n <= array_upper(regexp_split_to_array(str, E','),1))
) as ta;


+-------+
| arr   |
+-------+
| 22    |
| 22    |
| 333   |
| ddd   |
| aaa   |
| aaadd |
+-------+
6 rows in set

regexp_split_to_array 将字符串以某个分隔符拆分成array
array_upper array长度
generate_series 产生一个1..100的整数的表格,如果不支持此函数,可以用包含一个1..100个整数的表格