use of substring to get text within text
Posted on 2012-03-29
I have a field that collects lab test impression (lab results) text for each lab test. The field is named Results. This text can be in multiple lines for each lab test.
Here's an example:
Lab_test_ID Results_LINE Results
LAC20009127 1 Lab test group:
LAC20009127 2 "Third Trimester: Group B Streptococcus"
LAC20009127 4 IMPRESSION: Impression:
LAC20009127 5 screened group B strep
LAC20009127 6 Group B strep bacteria are present
LAC20009127 7 gestation 36 weeks
LAC20009127 8 susceptibility of GBS to erythromycin and clindamycin need to be determined
LAC20009127 10 Tester_id: tac145
LAC20009128 11 Auth_id: bgg236
I need to retrieve all text starting at "IMPRESSION: Impression:" and ending before the line of text with the Tester_id. All impression text ALWAYS start with the words "IMPRESSION: Impression:". The text with the words "IMPRESSION: Impression:" may or may not start at line 4 in the Lab_Results table. The lines with the text in between "IMPRESSION: Impression:" and "Tester_id:" is always variable (it's never at a static 6 lines like this example has.
How can I use substring or other string manipulation to get the text I need?
Thanks much in advance!