Parse String based on

• asha.bagesh

  Newbie

  Points: 5

  More actions

  April 5, 2012 at 4:00 am

  #96715

  Str 1 :

  ^0609|1|2834.56150|07710.32664|17.170|330.60|!

  Str 2 :

  FC03 00140700 00007D04 0002E702 0001624F 00C02A9D

  Str 3 :

  ^id=PTTRK30ES861001000245502&POS=<3.1><1259.429640><07735.382010><084038><260312>@

  They are all having the same type of data(U1 L1 L2 T1 T2 S1)

  U1|L1|L2|T1|T2|S1|! in Str 1 ( delimiter | )

  S1 U1 L1 L2 T2 T1 ins Str 2 (delimiter space)

  ^id=U1&POS=<T1><L1><L2><S1><T2> ( delimiter >< )

  I can have more than these string formats . these are stored in a db table called rawdata. I need to parse them and store them in db table which will have the fields (U1 L1 L2 T1 T2 S1) and table name is processeddata.

  How can I map these strings to the processeddata table. They have same type of info, but are in different positions in the string.

  Will creating another table

  create table fmt (

  StrFormat int,

  Delimiter varchar(10)null,

  [U1] int,

  [L1] int,

  [L2] int,

  [S1] int,

  [T1] int,

  [T2] int,

  )

  and populating with the positions help. how can i write a general query which will handle the format . The string can be recognized by the beginning part (^,^id=)

  insert into fmt values( 1,'|',19,3,4,7,5,6)

  insert into fmt values(2,' ',1,4,5,6,7,8)

  insert into fmt values (3,'><',1,3,4,5,6,7)

  Any other way to create a common parser ? ( Also the no of records in the rawdata is huge) So the query must be quick. Please help

  Thanks

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  April 5, 2012 at 7:49 am

  #1469519

  Take a look at the link in my signature. It is super fast.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/