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.