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个整数的表格
array_upper array长度
generate_series 产生一个1..100的整数的表格,如果不支持此函数,可以用包含一个1..100个整数的表格