Example of Text:
PROCEDURE: Cryo balloon antral pulmonary vein
isolation and cavotricuspid isthmus ablation.
The patient is a middle aged and happy.
I am trying to extract "Cryo balloon antral pulmonary vein isolation and cavotricuspid isthmus ablation" from the text.
The Code I used:
TRIM(REGEXP_SUBSTR(a.Document_Text, 'Procedure:\s*(.*)\s*?\.',1,1,'inm',1)) as Text_Procedure_DESC
But what I get is the whole note after 'Procedure:'
FYI:
Not sure where I messed up!
.*
pattern non-greedy by using .*?
m
option is redundant (as you are not matching the start or end of lines)TRIM
function.Like this:
SELECT REGEXP_SUBSTR(document_text, 'PROCEDURE:\s+(.*?)\.', 1, 1, 'in' , 1)
AS Text_Procedure_DESC
FROM table_name;
Which, for the sample data:
CREATE TABLE table_name (value) AS
SELECT 'PROCEDURE: Cryo balloon antral pulmonary vein
isolation and cavotricuspid isthmus ablation.
The patient is a middle aged and happy.' FROM DUAL
Outputs:
TEXT_PROCEDURE_DESC Cryo balloon antral pulmonary vein
isolation and cavotricuspid isthmus ablation
db<>fiddle here