I am looking at large strings in text. In the text alot of times have . or especially ? ? that serves as delimiters.
So what I want from the string 'Delivery Note ?Patient was at home. Delivered at home'
is Delivered at home BUT NOT Delivery Note ?Patient was at home which is what I am getting currently. Code below works well otherwise.
ADDED: Given the comment below, use of regular expressions is used here as there many permutations in the actual data source such as delivered at home, delivery in an ambulance, delivery in vehicle or born in an ambulance, born at home, etc, etc.
Any suggestions appreciated-
with txt as (select 1 as ID,
'Delivery Note ?Patient was at home. Delivered at home' as note_text from dual)
select txt.*,
regexp_substr(NOTE_TEXT, '(born|birth|sv(b|d)|deliver(ed|y|ing)|vbac)[^.|?|,|(|)|;|:|-].{1,24}(vehicle|bathroom|ambulance|home|[^a-z]car([^a-z|]|$))',1,1,'i')
as results
from txt;
Looks like this is what you need:
with txt as (select 1 as ID,
'Delivery Note ?Patient was at home. Delivered at home' as note_text from dual)
select txt.*,
regexp_substr(NOTE_TEXT, '(born|birth|sv(b|d)|deliver(ed|y|ing)|vbac)[^.?,)(;:-]{1,24}(vehicle|bathroom|ambulance|home|[^a-z]car([^a-z|]|$))',1,1,'i')
as results
from txt;
As you can see I removed |
in [^...], because you just need to specify excluded character without or
, and removed .
after it.
Result:
ID NOTE_TEXT RESULTS
---------- ----------------------------------------------------- --------------------
1 Delivery Note ?Patient was at home. Delivered at home Delivered at home