Oracle's SQL split text into rows and filter

Question

I have multi-line text data stored in table rows like this mock example here. Some of the rows are flagged as "IMPORTANT" and I'm trying to select a list of all the "IMPORTANT" rows along with corresponding IDs.

 select 10001 as id, 'some random text 11
 more random text IMPORTANT 12
 more random text IMPORTANT 13' as str
   from dual
  union all
 select 10002, 'other random text 21
 other random text IMPORTANT 22
 other random text 23'
   from dual;

I need something like this...

id      important text
10001   more random text IMPORTANT 12
10001   more random text IMPORTANT 13
10002   other random text IMPORTANT 22

I'd like this in a single SELECT, without any temp tables or cursors, and I've been looking at regexp_substr with connect by queries, but I seem to be stuck. Your help would be appreciated.

user5683823 · Accepted Answer · 2019-06-11 17:20:19Z

You could do something like this... Not entirely sure about the WHERE condition in the outer query, you can adjust that as needed (I allow IMPORTANT to appear in any capitalization, and I don't require that it be a single word - it may be part of the word "unimportant", for example, you will have to fix that) but I believe that's not the main issue you are asking about. The "main issue" is really solved in the subquery.

The WITH clause, of course, is not part of the solution (the SELECT query); it's there only to provide test data.

Read about the 'm' modifier (for "multi-line") - it allows ^ and $ to match at the beginning and end of each line of text, rather than the default (which is the beginning and end of the entire string, regardless of newline characters).

with
  test_data as (
     select 10001 as id, 'some random text 11
more random text IMPORTANT 12
more random text IMPORTANT 13' as str
   from dual
  union all
 select 10002, 'other random text 21
other random text IMPORTANT 22
other random text 23'
   from dual
)
select *
from   (
         select     id, regexp_substr(str, '^.*$', 1, level, 'm') as str_line
         from       test_data
         connect by level <= regexp_count(str, '^.*$', 1, 'm')
                and prior id =  id
                and prior sys_guid() is not null
       )
where upper(str_line) like '%IMPORTANT%'   -- modify this as needed
;

   ID  STR_LINE                        
-----  --------------------------------
10001  more random text IMPORTANT 12   
10001  more random text IMPORTANT 13   
10002  other random text IMPORTANT 22

Collectives™ on Stack Overflow

Oracle's SQL split text into rows and filter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related