0

I have a multi-line field in this Fiddle of Oracle 11g table. Using SQL, how can I extract the last line that matches 'Export'. This question helps with the regex for the last occurrence but I'm still not getting my desired results.

Desired Results

1,[27-SEP-12] NOT for export

2,[27-SEP-12] OK for export

Community
  • 1
  • 1
zundarz
  • 1,540
  • 3
  • 24
  • 40
  • 1
    Can't you fix the data so each line is in a separate record? – Gordon Linoff Feb 12 '14 at 20:44
  • Unfortunately not. This was converted data at customer's request. At one time they were in separate records. Customer wanted all in a newer column with more column width. Now a third party needs information as if it were in the old table.... – zundarz Feb 12 '14 at 21:06
  • I'm aware, this is what happens when parties to a project are unaware of a change to the project. – zundarz Feb 12 '14 at 21:09

1 Answers1

2
select 
  customer_id,
  regexp_substr(exp_comment,'.*'||chr(10)||'(.*Export)', 1, 1, 'n', 1) as export
from export_comments

fiddle

Egor Skriptunoff
  • 23,359
  • 2
  • 34
  • 64
  • Egor, thanks for the response. I'm using Oracle's specs http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm to help explain what is happening. What does the final 1 just before the closing parenthesis do? – zundarz Feb 13 '14 at 16:34
  • 1
    You are looking at Oracle 10 docs. Last parameter appeared in Oracle 11. – Egor Skriptunoff Feb 13 '14 at 17:04