2
\$\begingroup\$

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.

\$\endgroup\$
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ I understand that SQL/Database is not meant to parse text but I want to avoid unnecessary data processing on the server which could be taken care of at the database end, if possible. \$\endgroup\$ – Vivek Jain Aug 29 '13 at 6:20
  • 1
    \$\begingroup\$ You should indeed avoid this if possible, but it is either this, or redesigning your schema (the most correct way to go about it), or using some twisted sql coding as you did (which might be a big performance hindrance (?)). \$\endgroup\$ – toto2 Aug 29 '13 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.