Split string with multiple delimiters into columns in SQL Server

Question

I have data in my SQL Server table in string format that looks like this:

screenshot

Sample data

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');

I want a SQL query to get an output like this:

account_id	resource_type	resource_name
535533456241	buckets	tni-prod-diva-backups
460085747812	buckets	bda-sit-tims
123456789012	buckets	fi.fa.foo.bar.baz

I am working with this code, but its not giving me the expected output. Could someone help?

SELECT 
    REVERSE(PARSENAME(REPLACE(REVERSE([resource_type]), ',', '.'), 1)) AS [Street],
    REVERSE(PARSENAME(REPLACE(REVERSE([resource_type]), ',', '.'), 2)) AS [Street],
    REVERSE(PARSENAME(REPLACE(REVERSE([resource_type]), ',', '.'), 3)) AS [Street]
FROM
    [Test].[CloudHealth]

Output of this query:

Answer 1

If that string is transformed back to a JSON format, then it's possible to process it like a 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	resource_type	resource_name
535533456241	buckets	tni-prod-diva-backups
460085747812	buckets	bda-sit-tims
123456789012	buckets	fi.fa.foo.bar.baz

Tests on db<>fiddle here