CodePudding user response:

; With cte as (
Select * from
(values
Test (' 42 ', 'zhang SAN (Auth: BASIC: 1, the Admin: SAME_ACCOUNT_ID: 0, Customer: BASIC: 1]'),
49 '(',' li si test [Customer: SAME_ACCOUNT_ID: 1, Frozen: BASIC: 1] '),
Conger chicken (' 10 ', 'test try [Frozne: SAME_ACCOUNT_ID: 1, the Admin: BASIC: 1, Anon: BASIC: 0]')

) tt (id, mation)

)
Take []/* */
content between, cte1 as (
Select id, substr (mation, strpos (mation, '[') + 1, strpos (mation,'] ') - strpos (mation, '[') - 1) as mation
The from cte
)
/* will mation column demolition multi-line */
, cte2 as (
Select id, regexp_split_to_table (mation, ', ') as mation
The from cte1
)
Select id | | ', '| | the replace (mation,' : ', ', ') as the results
The from cte2