SQL字符串数据1行拆分为多行

需求：将字符串数据拆分为多行

思路：首先将字符串转成数组格式，然后使用 cross join 函数即可实现

1、原始数据（字符串格式）

SELECT distinct
  'A' as name,
  'Hello,World' as text
from
  users
union all
SELECT distinct
  'B' as name,
  'Hello,World,leo' as text
from
  users

结果：

 

2、字符串转数组

使用split函数，split (text, ',') as text1

执行结果：

 

将以上数据临时存为a1表

with
  a1 as (
    SELECT distinct
      'A' as name,
      split ('Hello,World', ',') as text1
    from
      users
    union all
    SELECT distinct
      'B' as name,
      split ('Hello,World,leo', ',') as text1
    from
      users
  )

3、拆分为多行

select
  name,
  new_text,
  text1
from
  a1
  cross join unnest (text1) as t (new_text)

结果数据：

end！