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 BVRFROM DataTableWHERE DD is not nullI 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.
Extract data from one column of SQL Server table
c#;asp.net;sql server;etl