?
Solved

use of substring to get text within text

Posted on 2012-03-29
8
Medium Priority
?
514 Views
Last Modified: 2012-03-31
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
0
Comment
Question by:wdelaney05
  • 3
  • 3
7 Comments
 
LVL 27

Accepted Solution

by:
sujith80 earned 1000 total points
ID: 37782759
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
 

Author Comment

by:wdelaney05
ID: 37782871
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 37782960
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:sujith80
ID: 37785376
that's ridiculous to be given an assist for the solution
0
 

Author Comment

by:wdelaney05
ID: 37785482
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
 

Author Comment

by:wdelaney05
ID: 37785507
sujith80, I've emailed a request to customer service requesting an adjustment in the points awarded to the more appropriate answer.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 37790900
Thank You, wdelaney05
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question