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 |
|
sharpholly
Starting Member
2 Posts |
 Posted - 2010-12-20 : 00:48:36
|
| Hello,I am trying to put together a SELECT SUBSTRING statement, atleast that's what I think I need here.I have table 'customers' with the field 'information' which houses each customers unique Transaction ID; however it is stored in a text field with varying lengths of text before the text I would like to extract 'Transaction ID: #####'Any suggestions would be greatly appreciated, I am an eager noob with much to learn. Thanks! |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-12-20 : 04:25:49
|
| Can you post the sample data of the the field "information" and the expected o/p ?PBUH |
 |
|
|
sharpholly
Starting Member
2 Posts |
Posted - 2010-12-20 : 10:50:59
|
| Sample Data:<p>Note From Bob</p><p>This is a note section with varying length notes....</p><p>NAMES: John Smith, Jane Smith, , , , , , , , , , <br />CELL: 321-555-4321<br />TRANSACTION_ID: 3357749572<br /> <br />TRIP: 102 - Orlando To Miami<br />DEPARTURE DATE: Saturday 25th of December 2010<br />SEATS NEEDED: 2<br />DEPARTURE STATION:<br />TRIP: 102 - 11:00 AM - You Specify Address, Orlando, FL 0<br />DEPARTURE INFO: 123 My Street Rd, Orlando,FL,32819 <br />ARRIVAL STATION:<br />TRIP: 102 - 4:30 PM - You Specify Address, Miami, FL 0<br />ARRIVAL INFO: 321 My Street Rd, Miami,FL,33139<br />DISPATCH NUMBER FOR THIS TRIP ONLY IS: ABC Company 888-777-6543<br />**********White vehicle with "Trip" on the side of it**********<br /> <br /> <br /></p><div style="border: 1px solid #000; padding: 5px; width: 450px; height: 120px; font-size: 8pt;"><span style="width: 200px; float: left;">......on it goes for about 100 characters or so with personal information.I am trying to extract the Transaction_ID:#####The character count preceding the Transaction_ID varies because of the 'notes section' before it.However, the Transaction ID:##### always has the same character count.Hope this helps clarify myself. |
|
|
|
akira220984
Starting Member
13 Posts |
Posted - 2010-12-20 : 12:01:32
|
| hi you could use this and modify as you need.declare @stuff varchar(max)set @stuff = '<p>Note From Bob</p><p>This is a note section with varying length notes....</p><p>NAMES: John Smith, Jane Smith, , , , , , , , , , <br />CELL: 321-555-4321<br />TRANSACTION_ID: 3357749572<br /> <br />TRIP: 102 - Orlando To Miami<br />DEPARTURE DATE: Saturday 25th of December 2010<br />SEATS NEEDED: 2<br />DEPARTURE STATION:<br />TRIP: 102 - 11:00 AM - You Specify Address, Orlando, FL 0<br />DEPARTURE INFO: 123 My Street Rd, Orlando,FL,32819 <br />ARRIVAL STATION:<br />TRIP: 102 - 4:30 PM - You Specify Address, Miami, FL 0<br />ARRIVAL INFO: 321 My Street Rd, Miami,FL,33139<br />DISPATCH NUMBER FOR THIS TRIP ONLY IS: ABC Company 888-777-6543<br />**********White vehicle with "Trip" on the side of it**********<br /> <br /> <br /></p><div style="border: 1px solid #000; padding: 5px; width: 450px; height: 120px; font-size: 8pt;"><span style="width: 200px; float: left;">......'select substring(@stuff,CHARINDEX('transaction_id',@stuff),26)returns:TRANSACTION_ID: 3357749572Regards,Ric |
|
|
|
|
|
|
|
|
SELECT SUBSTRING