Extract data from one column of SQL Server table

Question

I have an SQL Server table from which I want to extract data from one column (DD) and return as multiple columns.

The data is in NVARCHAR and looks like:

;<b>HF</b>:GN;<b>HM</b>:GTN;<b>HN</b>:GN;<b>PN</b>:AN;<b>PVD</b>:GO;<b>PVR</b>:1.1;<b>BN</b>:AN;<b>BVD</b>:GO;<b>BV</b>:2.3;

There is less to be changed in the way this data is structured.

I have to extract all of the data in <b>...</b> and between : and ; and have to return as individual columns. I tried with the following query:

SELECT DataID
    ,SUBSTRING(DD,CHARINDEX(';<b>HF</b>:',DD) + LEN(';<b>HF</b>:'),CHARINDEX(';<b>HM</b>:',DD) - (CHARINDEX(';<b>HF</b>:',DD) + LEN(';<b>HF</b>:'))) as HF
    ,SUBSTRING(DD,CHARINDEX(';<b>HM</b>:',DD) + LEN(';<b>HM</b>:'),CHARINDEX(';<b>HN</b>:',DD) - (CHARINDEX(';<b>HM</b>:',DD) + LEN(';<b>HM</b>:'))) as HM
    ,SUBSTRING(DD,CHARINDEX(';<b>HN</b>:',DD) + LEN(';<b>HN</b>:'),CHARINDEX(';<b>PN</b>:',DD) - (CHARINDEX(';<b>HN</b>:',DD) + LEN(';<b>HN</b>:'))) as HN
    ,SUBSTRING(DD,CHARINDEX(';<b>PN</b>:',DD) + LEN(';<b>PN</b>:'),CHARINDEX(';<b>PVD</b>:',DD) - (CHARINDEX(';<b>PN</b>:',DD) + LEN(';<b>PN</b>:'))) as PN
    ,SUBSTRING(DD,CHARINDEX(';<b>PVD</b>:',DD) + LEN(';<b>PVD</b>:'),CHARINDEX(';<b>PVR</b>:',DD) - (CHARINDEX(';<b>PVD</b>:',DD) + LEN(';<b>PVD</b>:'))) as PVD
    ,SUBSTRING(DD,CHARINDEX(';<b>PVR</b>:',DD) + LEN(';<b>PVR</b>:'),CHARINDEX(';<b>BN</b>:',DD) - (CHARINDEX(';<b>PVR</b>:',DD) + LEN(';<b>PVR</b>:'))) as PVR
    ,SUBSTRING(DD,CHARINDEX(';<b>BN</b>:',DD) + LEN(';<b>BN</b>:'),CHARINDEX(';<b>BVD</b>:',DD) - (CHARINDEX(';<b>BN</b>:',DD) + LEN(';<b>BN</b>:'))) as BN
    ,SUBSTRING(DD,CHARINDEX(';<b>BVD</b>:',DD) + LEN(';<b>BVD</b>:'),CHARINDEX(';<b>BVR</b>:',DD) - (CHARINDEX(';<b>BVD</b>:',DD) + LEN(';<b>BVD</b>:'))) as BVD
    ,SUBSTRING(DD,CHARINDEX(';<b>BVR</b>:',DD) + LEN(';<b>BVR</b>:'),LEN(DD) - (CHARINDEX(';<b>BVR</b>:',DD) + LEN(';<b>BVR</b>:'))) as BVR
FROM DataTable
WHERE DD is not null

I think I'll have to generate the query at run-time from a list of column names or may have hard-coded query as the structure of the data and the required columns is not likely to change.

Given that, is there a better approach to optimize this query? May be by using Regular Expressions or any other approach?

Edit:

Is there a better approach using C# Code-behind? This eventually is being used for an ASP.Net Web Application.

Answer 1

Basically, sql is not meant to parse text. The database design is the issue here and it probably needs to be modified. Another option is to return the string to the server and do the parsing there and then make another call to sql.

My answer is basically a copy of Andrew White's answer to this stackoverflow question.