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?
 
SujithConnect With a Mentor Data 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
 
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)Connect With a Mentor 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.