Solved

use of substring to get text within text

Posted on 2012-03-29
8
495 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
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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now