Pull out string between two words in string column [message #674683] |
Sat, 09 February 2019 14:20 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/601bb5a7497f315ebc65c92727142bfb?s=64&d=mm&r=g) |
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 #674686 is a reply to message #674683] |
Sat, 09 February 2019 14:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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.
|
|
|
|