Hi @RJ,
Please try the following solution.
-- DDL and sample data population, start
DECLARE @tbl table (id int IDENTITY PRIMARY KEY, details varchar(255) NULL);
INSERT INTO @tbl (details) VALUES
('d=4; a=1; b=2;e=5'),
('a=1;d=4'),
('c=3; b=2; a=1');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = ';';
-- Method #1
select t.*
, a = SUBSTRING(c.value('(/root/r[substring(text()[1],1,1)="a"]/text())[1]', 'VARCHAR(10)'), 3,10)
, b = SUBSTRING(c.value('(/root/r[substring(text()[1],1,1)="b"]/text())[1]', 'VARCHAR(10)'), 3,10)
, c = SUBSTRING(c.value('(/root/r[substring(text()[1],1,1)="c"]/text())[1]', 'VARCHAR(10)'), 3,10)
, d = SUBSTRING(c.value('(/root/r[substring(text()[1],1,1)="d"]/text())[1]', 'VARCHAR(10)'), 3,10)
, e = SUBSTRING(c.value('(/root/r[substring(text()[1],1,1)="e"]/text())[1]', 'VARCHAR(10)'), 3,10)
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +
REPLACE(REPLACE(details,SPACE(1),''), @separator, ']]></r><r><![CDATA[') +
']]></r></root>' AS XML)) AS t1(c);
-- Method #2
SELECT t.*
, a = JSON_VALUE(j,'$[0].a')
, b = JSON_VALUE(j,'$[0].b')
, c = JSON_VALUE(j,'$[0].c')
, d = JSON_VALUE(j,'$[0].d')
, e = JSON_VALUE(j,'$[0].e')
FROM @tbl
CROSS APPLY (SELECT TRY_CAST('[{"' + REPLACE(REPLACE(REPLACE(details,SPACE(1),'')
,';',',"')
,'=','":') + '}]' AS NVARCHAR(MAX))
) AS t(j);