Solved

use of substring to get text within text

Posted on 2012-03-29
8
507 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 27

Accepted Solution

by:
sujith80 earned 250 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

635 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