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 Sat, 09 February 2019 14:20
 spalato76 Messages: 2Registered: 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:

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
 BlackSwan Messages: 26564Registered: January 2009 Location: SoCal Senior Member
Welcome to this forum

OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

LONG changed to CLOB.
Re: Pull out string between two words in string column [message #674686 is a reply to message #674683] Sat, 09 February 2019 14:48
 Michel Cadot Messages: 66471Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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

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
 spalato76 Messages: 2Registered: 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 Jul 18 14:34:11 CDT 2019