View that is parsing out a column 1

[dusanv]

Hello,

I have a table that would look like this simplified:

EmpId Data
1 Name|Dusan|Title|Programmer|

every odd token in Data column string is a field name, and every even one is a field value. So, Name=Dusan, Title=Programer.

What I need to do is create a View that would look like this:

EmpId Name Title
1 Dusan Programmer

Is this possible to achieve? I've read about extended procedures where you can interface with C or C++ which could do the parsing part but I'm still not sure if this will help solve my problem. Any suggestions appreciated -- thanks!

 

[fluteplr]

If you are running sql 2000 you could create a user function that could retrieve the psuedo field value.

Like

create function dbo.fnpsuedofield(@fname varchar(50), @data varchar(1000))

returns varchar(100)
as

set @fname = '%'+@fname+'|%'

set @data =
substring(@data,patindex(@fname,@data)+len(@fname)-2,
len(@data))


set @data = substring(@data,1,charindex('|',@data)-1)

return @data


However, I cannot imagine any way you are going to get decent performance on this. Why on earth are you storing your data this way? If you are getting it from some other program in this format, write a program to parse the data out BEFORE you put it in sql server. And store the data in a normal fashion.

 

[dusanv]

fluteplr , thanks for pointing me in the right direction -- I do run SQL 2000 so I will give your approach as shot. However, I will have to expand on it considerably because my problem is more complex than the simplified example above: I cannot count on field name/value pairs to be in any order and also, one row can have "fields" that other doesn't. For example:

EmpId Data
1 Name|Dusan|Title|Programmer|
2 Comment|Helpful Lad|Title|Programer|Name|fluteplr

My view still has to have ordered columns like this:

EmpID Name Title Comment

So it looks like I will have to hardcode view column names (not sure how to do that) and have the udf return values associated with the column name.

As for your comment about oddity of storing data in this fashion...I couldn't agree with you more, but unfortunately I am stuck with this design. It has to do with the requirement for our application to write this data "on the fly" without columns being predefined.