Home » SQL & PL/SQL » SQL & PL/SQL » Pull out string between two words in string column (Oracle 11g)
Pull out string between two words in string column [message #674683] Sat, 09 February 2019 14:20 Go to next message
spalato76
Messages: 2
Registered: February 2019
Junior Member
Hi All,

I have a large string column, due to the size of the string I had to set it as a "CLOB" datatype. I'm trying to get a string expression between two words as you'll see in the example below. I'm trying to get the string expression between the words "IMPRESSIONS:" and "RECOMMENDATIONS:" and once I get the string value between these two words I have to determine if there is any value other than "\.br\" in that string. There is supposed to be values other than this and if there isn't that means the string is null and there is missing data. As you'll see there can be multiple occurances of "\.br\", I don't really care about the number of occurrences, I just need to know if there is any string other than this otherwise it's considered null and needs to be identified. The oracle version is 11g and the table has two columns:

column datatype

primary_key number

hl7_message clob

The hl7_message column is the one with the string and here's two examples of the string with info and without:

With info:

\ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\1. Possible residual polyp in ascending colon site (across from ICV)\.br\2. Transverse colon diminutive polyp\.br\3. Moderate pan and severe left sided diverticulosis.\.br\\.br\\.br\RECOMMENDATIONS: A recommendation for ongoing surveillance will be provided\.br\to the referring physician after pathological examination of today's specimens.\.br\The recommendation will be provided within the next 6 - 8 weeks.

Without info:

\ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\\.br\RECOMMENDATIONS:\.br\\.br\_____________________________\.br\

Any help would be appreciated.

Thanks

P.S. the " \.br\" is just line breaks since the info is being inputed through an online application.
Re: Pull out string between two words in string column [message #674685 is a reply to message #674683] Sat, 09 February 2019 14:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

LONG changed to CLOB.
https://community.oracle.com/thread/4200287
Re: Pull out string between two words in string column [message #674686 is a reply to message #674683] Sat, 09 February 2019 14:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (id integer, val clob);

Table created.

SQL> insert into t values (1, q'[\ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\1. Possible residual polyp in ascending
 colon site (across from ICV)\.br\2. Transverse colon diminutive polyp\.br\3. Moderate pan and severe left sided diverti
culosis.\.br\\.br\\.br\RECOMMENDATIONS: A recommendation for ongoing surveillance will be provided\.br\to the referring
physician after pathological examination of today's specimens.\.br\The recommendation will be provided within the next 6
 - 8 weeks.]');

1 row created.

SQL> insert into t values (2, q'[\ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\\.br\RECOMMENDATIONS:\.br\\.br\________
_____________________\.br\]');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;
        ID
----------
VAL
------------------------------------------------------------------------------------------------------------------------
         1
\ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\1. Possible residual polyp in ascending colon site (across from ICV)\.br
\2. Transverse colon diminutive polyp\.br\3. Moderate pan and severe left sided diverticulosis.\.br\\.br\\.br\RECOMMENDA
TIONS: A recommendation for ongoing surveillance will be provided\.br\to the referring physician after pathological exam
ination of today's specimens.\.br\The recommendation will be provided within the next 6 - 8 weeks.
         2
\ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\\.br\RECOMMENDATIONS:\.br\\.br\_____________________________\.br\

2 rows selected.

SQL> select id,
  2         decode(
  3           replace(
  4            to_char(regexp_substr(val,'IMPRESSIONS:(.*)RECOMMENDATIONS:',1,1,'',1)),
  5            '\.br\'),
  6           NULL, 'No info', 'Info is there') result
  7  from t
  8  /
        ID RESULT
---------- -------------
         1 Info is there
         2 No info

2 rows selected.
Re: Pull out string between two words in string column [message #674687 is a reply to message #674686] Sat, 09 February 2019 23:50 Go to previous message
spalato76
Messages: 2
Registered: February 2019
Junior Member
Thanks Michel, that worked perfectly, appreciate the help!
Previous Topic: Issue in dynamic sql.
Next Topic: Tree View, Connect by Prior on 100m records
Goto Forum:
  


Current Time: Thu Mar 28 12:35:47 CDT 2024