use of substring to get text within text

Hi there,
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      3      
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      9      
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!
Best regards,
wdelaney
wdelaney05Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
Assuming you are on 11g, use the below
SQL> select * from tbl1;

LAB_TEST_ID          RESULTS_LINE RESULTS
-------------------- ------------ --------------------------------------------------------------------------------
LAC20009127                     1 Lab test group:
LAC20009127                     2 "Third Trimester: Group B Streptococcus"
LAC20009127                     3
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                     9
LAC20009127                    10 Tester_id: tac145
LAC20009128                    11  Auth_id: bgg236

11 rows selected.

SQL>
SQL> select replace(regexp_substr(results_c, 'IMPRESSION.*?Tester_id:[^~]+', 1,1,'i'), '~', chr(10)) results
  2  from(
  3  select lab_test_id, listagg(results, '~') within group (order by results_line) results_c
  4  from tbl1
  5  group by lab_test_id
  6  )
  7  where results_c like '%IMPRESSION%';

RESULTS
--------------------------------------------------------------------------------
IMPRESSION: Impression:
screened group B strep
Group B strep bacteria are present
gestation 36 weeks
susceptibility of GBS to erythromycin and clindamycin need  to be determined
Tester_id: tac145

Open in new window


I have made a couple of assumptions
1. You are on 11g. If not you can replace the listagg with any string concatenation techniques. Plenty of them are available on the web
2. Your strings doesnt contain a '~' character, if so you may replace it with something that doesnt appear in your strings.
3. I have finally separated each string into separate lines using chr(10), you may remove it if you want.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wdelaney05Author Commented:
Thanks, sujith80!
I hesitate to use LISTAGG because I think LISTAGG may have character limitations to it (I think it's 4000 characters. My text may be longer than 4000 characters sometimes).
I'll have to look around for other string concatenation techniques.
0
slightwv (䄆 Netminder) Commented:
Check out the XML aggregation.  It can return a CLOB.

http://www.experts-exchange.com/Database/Oracle/Q_24914739.html?#a25864822

Just change getstringval() with getclobval() and of course the ',' delimiter to a space.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SujithData ArchitectCommented:
that's ridiculous to be given an assist for the solution
0
wdelaney05Author Commented:
ah, apologies, sujith80! I'm not used to the way things are awarded points on the site. I liked both answers - both could work in my situation. I thought if I split the points, both would be "accepted". I agree with you, the first answer should be the accepted one.
0
wdelaney05Author Commented:
sujith80, I've emailed a request to customer service requesting an adjustment in the points awarded to the more appropriate answer.
0
SujithData ArchitectCommented:
Thank You, wdelaney05
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.