在SQL Server中将带有多个分隔符的字符串拆分为列

Question

我的SQL Server表中有字符串格式的数据，如下所示：

屏幕截图

样本数据

create table Test (
resource_type varchar(300)
);
insert into Test (resource_type) values
('account_id:535533456241,resource_type:buckets,resource_name:tni-prod-diva-backups'), 
('account_id:460085747812,resource_type:buckets,resource_name:bda-sit-tims'), 
('account_id:123456789012,resource_type:buckets,resource_name:fi.fa.foo.bar.baz');

我想要一个SQL查询得到这样的输出：

tni proddiva备份bda坐时间fi.fa.foo.baz

account_id	resource_type	resource_name
535533456241	存储桶
460085747812	桶
123456789012	桶

如果该字符串被转换回JSON格式，那么就可以像处理JSON一样处理它。

SELECT 
JSON_VALUE(json, '$.account_id')    AS [account_id]
, JSON_VALUE(json, '$.resource_type') AS [resource_type]
, JSON_VALUE(json, '$.resource_name') AS [resource_name]
FROM Test t
CROSS APPLY (VALUES('{"'+REPLACE(REPLACE(STRING_ESCAPE(t.resource_type,'json'),':','":"'),',','","') +'"}')) ca(json)

account_id
535533456241		460085747812		12346789012