Comma separated data i need to extract...

Question

Hi to all,

I have a problem regarding data that they gave.

The data contains comma separated, example

,Title = yahoo,AssetSummary = website,ExpirationDate = 02/19/2010,DateCreated = 02/18/2010,UsageRightsSummary = test

I need to get the AssetSummary, ExpirationDate and the UsageRightsSummary.

I know the substring and the charindex. But i'm looking for the easiest way and efficient.

 

Regards,

Ian

 

---

3 stars and a sun

Answer 1

Data that "they" gave? They as in who and how did they give you this data?

Do you get this data in a flat file? In some feed?

What volumes are you dealing with?

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 2

Hi,

Actually it's on the database... I restored it into my local machine.

the field is a SearchText Enabled. (unfortunately i don't have any idea of full searchtext yet).

about 100 thousand of data in production.

---

3 stars and a sun

Answer 3

Well, you can split the data first by the comma into separate rows. Then split each row into two Attribute Value based on the = sign.

For the first task (splitting by using a comma) you can use any of the split functions available

take a look here http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx#comments

and once you split on a comma, splitting by Attribute and value is a simple task. Then you populate the actual table for each of the case of the Attribute.

Sounds straightforward enough, although you may try writing the whole parsing process as one CRL table valued function to return what we want as once (without step by step operation).

 

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog