I have a large string column, due to the size of the string I had to set it as a "LONG" 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 occurances, 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:
The hl7_message column is the one with the string and here's two examples of the string with info and without:
\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.
\ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\\.br\RECOMMENDATIONS:\.br\\.br\_____________________________\.br\
Any help would be appreciated.
P.S. the " \.br\" is just line breaks since the info is being inputed through an online application.
LONG datatype has been deprecated since at least V8 Oracle last Century.
I won't facilitate Worst Practice coding.
You should use CLOB datatype instead.
Please click on URL below & provide details as stated in #5 - #9 inclusive
Why so MANY UNANSWERED threads started by you?
Don't use the LONG data type.
If you already used the LONG data type, un-use it. If you don't know how to do that, learn (and come back here and ask if you need help).
You should do this regardless of any other question you have, for example the one you asked in the original post in this thread.
* * * * * *
Once that's done, here is how you could solve the problem you asked. Assuming the message column is CLOB data type:
prep as (
, instr(hl7_message, 'IMPRESSIONS:') + length('IMPRESSIONS:') as pos1
, instr(hl7_message, 'RECOMMENDATIONS:') as pos2
where dbms_lob.compare(hl7_message, rpad(to_clob('\.br\'), pos2 - pos1, '\.br\'), pos2 - pos1, pos1, 1) = 0
and mod(pos2 - pos1, length('\.br\')) = 0
or pos2 = pos1