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.

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. — Yitzhak Khabinsky, Apr 21 '23 at 18:30
This is not clear -- are there actually CR (13) carrage return characters in the text string? Is the ID column really a string -- or do you have a period there by mistake? — Hogan, Apr 21 '23 at 18:34

score 0 · Answer 1 · answered Apr 21 '23 at 19:12

As others have stated, you should provide more data. But I will take a guess at what you want. Note that there are quite a few assumptions here.

Either way, this is what I would do.

Note that depending on indexes, volume of data, etc., you may need to use interim tables (I personally like to use temp tables).

IF OBJECT_ID(N'tempdb..#t') IS NOT NULL DROP TABLE #t
SELECT t.* INTO #t
FROM (
SELECT 1 AS ID,
'Name: abc
Company: cuz
Address: dfg' AS [Text]
UNION
SELECT 2 AS ID,
'Name: xyz
Company: !!!
Address: something else' AS [Text]
UNION
SELECT 3 AS ID,
'Name: qwe
Company: @@@
Address: 123456780' AS [Text]
    ) AS t

--Identify break character
DECLARE @BreakCharacter NVARCHAR(100) = CHAR(13)

--Normalize the data so that there are not multiple "breaks"
UPDATE #t
SET [Text] = REPLACE(REPLACE([Text], CHAR(13) + CHAR(10),CHAR(13)), CHAR(10), CHAR(13))

--use CTE to iterate over the text and break it apart
;WITH t AS (
SELECT ID, CAST([Text] AS NVARCHAR(100)) AS [Text], CAST([Text] AS NVARCHAR(100)) AS Remaining, 0 AS Iteration
FROM #t
UNION ALL
SELECT ID,
    CAST(CASE WHEN Remaining LIKE '%' + @BreakCharacter + '%' THEN LEFT(Remaining, CHARINDEX(@BreakCharacter, Remaining)-1) ELSE Remaining END  AS NVARCHAR(100)),
    CAST(CASE WHEN Remaining LIKE '%' + @BreakCharacter + '%' THEN STUFF(Remaining, 1, CHARINDEX(@BreakCharacter, Remaining),'') END            AS NVARCHAR(100)),
    Iteration + 1
FROM t
WHERE Remaining IS NOT NULL 
)
SELECT *
FROM(
    SELECT
        ID,
        LTRIM(RTRIM(LEFT([Text],CHARINDEX(':',[Text])-1))) AS Field,
        STUFF([Text],1,CHARINDEX(':',[Text]),'') AS Value
    FROM t
    WHERE Iteration > 0
) AS p--Alias to pivot
PIVOT (MAX(Value) FOR Field IN ([Name], [Company],[Address])) AS t--alias for the final dataset

John Cappelletti · Accepted Answer · 2023-04-24T20:44:50.027

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

I tried this method and realized that my input data is not separated on a new line but it is just separated by space like this ID. Text 1 NAME: abc. COMPANY: cuz. ADDRESS: dfg — Sue_sue, Apr 24 '23 at 18:24

Extract data under subheadings from a text column in SQL Server

2 Answers2