Wednesday, August 11, 2010 1:56 AM
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.
3 stars and a sun
Wednesday, August 11, 2010 2:09 AMModerator
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)
Wednesday, August 11, 2010 2:18 AM
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
Wednesday, August 11, 2010 2:34 AMModerator
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
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
- Marked As Answer by KJian_ Tuesday, August 17, 2010 8:58 AM