uj5u.com熱心網友回復：
;with cte as (select * from
(values
('42','測驗張三[Auth:BASIC:1,Admin:SAME_ACCOUNT_ID:0,Customer:BASIC:1]'),
('49','李四測驗[Customer:SAME_ACCOUNT_ID:1,Frozen:BASIC:1]'),
('10','測黃燜雞試[Frozne:SAME_ACCOUNT_ID:1,Admin:BASIC:1,Anon:BASIC:0]')
) tt (id,mation)
)
/*取[]之間的內容*/
,cte1 as (
select id,substr(mation,strpos(mation,'[')+1,strpos(mation,']')-strpos(mation,'[')-1) as mation
from cte
)
/*將mation一列拆多行*/
,cte2 as (
select id,regexp_split_to_table(mation,',') as mation
from cte1
)
select id||','||replace(mation,':',',') as 結果
from cte2
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/35019.html
標籤：PostgreSQL
上一篇：大佬求解