Extract data under subheadings from a text column in SQL Server

Question

I have a text field in a table. The field has subheadings and I want to extract the data under each subheading and create a columns named after the subheading.

For example:

ID.      Text
1        NAME: abc.  COMPANY: cuz.  ADDRESS: dfg

Required output:

ID  Name Company Address
1.   abc  Cuz    dfg

I tried substring with charindex to get the position of the Subheading, but I am unable to get the length right. The text under each subheading is of variable length.

Answer 1

Updated considering new information. In the future, please be more careful and considerate

Example

Declare @YourTable Table ([ID] varchar(50),[Text] varchar(50))  Insert Into @YourTable Values 
 (1,'Name: abc Company: cuz Address: dfg')
,(2,'Name: xyz Company: bis Address: Fabtown')
 
Select ID
      ,C.*
 From @YourTable A
 Cross Apply ( values ( replace(replace([Text],'Company:','||Company:'),'Address:','||Address:') ) ) B(NewStr)
 Cross Apply (
    Select Name    = ltrim(rtrim(replace(max(case when Value like '%Name:%'    then Value end),'Name:'   ,'')))
          ,Company = ltrim(rtrim(replace(max(case when Value like '%Company:%' then Value end),'Company:','')))
          ,Address = ltrim(rtrim(replace(max(case when Value like '%Address:%' then Value end),'Address:','')))
     From  OpenJSON( '["'+replace(string_escape(NewStr,'json'),'||','","')+'"]' )
 ) C

Results

ID  Name    Company Address
1   abc     cuz     dfg
2   xyz     bis     Fabtown