Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
satheesh
Posting Yak Master
152 Posts |
 Posted - 2014-06-17 : 04:42:03
|
| Hi All,I have a long text in 'Quote' column as below and i have to extract Trip Duration, Destination and Base Rate from this text. The ‘Base Rate’ will be repeated throughout the text if there is more than one traveler and I only need the first instance. Begin Quote Calculation<br />\n<br />\....<br />\nAgent Id: 001<br />\nTrip Duration: 5days<br />\nRelationship Type: Individual<br />....nDestination: AreaTwo<br />\n<br ...../>\Resolved Trip Type To: 1 with Trip Subtype: 0<br />\nResolved Relationship: Individual....... />\n*Base Rates*<br />\nBase Rate: 6.070000<br />\.....\Resolved Trip Type To: 2 with Trip Subtype: 0<br />\nResolved Relationship: Individual....... />\n*Base Rates*<br />\nBase Rate: 9.070000<br />\.....ResultTrip Duration: 5 daysDestination: AreaTwoBase Rate: 6.070000Any help will be highly appreciated.ThanksSG |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-06-17 : 07:40:32
|
| Is this in a SQL field in a SQL table, you can use the Like wildcard to pull the text you want then you can wrap it all in a CTE to pull only the first result.We are the creators of our own reality! |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-17 : 10:59:33
|
quote:
Originally posted by satheesh
Hi All,I have a long text in 'Quote' column as below and i have to extract Trip Duration, Destination and Base Rate from this text. The ‘Base Rate’ will be repeated throughout the text if there is more than one traveler and I only need the first instance. Begin Quote Calculation<br />\n<br />\....<br />\nAgent Id: 001<br />\nTrip Duration: 5days<br />\nRelationship Type: Individual<br />....nDestination: AreaTwo<br />\n<br ...../>\Resolved Trip Type To: 1 with Trip Subtype: 0<br />\nResolved Relationship: Individual....... />\n*Base Rates*<br />\nBase Rate: 6.070000<br />\.....\Resolved Trip Type To: 2 with Trip Subtype: 0<br />\nResolved Relationship: Individual....... />\n*Base Rates*<br />\nBase Rate: 9.070000<br />\.....ResultTrip Duration: 5 daysDestination: AreaTwoBase Rate: 6.070000
Can you describe, in words, the logic you want to apply to the string in order to parse out each value? |
|
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-06-18 : 05:20:42
|
| You will need to find your text place number for each word you want to return but you can use SUBSTRING other than that you can use the CHARINDEX function. Select Substring(yourfield,4,11) as Text1, Substring(yourfield, 17, 7) as Text2, Substring(yourfield, 88, 7) as Text3, Substring(yourfield, 146,7) as Text4 From dbo.YourTableWe are the creators of our own reality! |
|
|
|
|
|
|
|
|
String extract