none
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
    Wednesday, August 11, 2010 1:56 AM

Answers

  • 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
    • Marked as answer by KJian_ Tuesday, August 17, 2010 8:58 AM
    Wednesday, August 11, 2010 2:34 AM
    Moderator

All replies

  • 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:09 AM
    Moderator
  • 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
    Wednesday, August 11, 2010 2:18 AM
  • 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
    • Marked as answer by KJian_ Tuesday, August 17, 2010 8:58 AM
    Wednesday, August 11, 2010 2:34 AM
    Moderator