apex_string.split new line as delimiter

Question

I am using this query to split a string of a text area field inside my SQL query:

select * from "MyTable" a 
where NAME in (select * from 
TABLE(apex_string.split(:P23_TEXTAREA, ',')))

It works but I would like to split the string with a new line delimiter instead of a comma. I tried already "\n", "\r\n", "
" but without success. If I remove the delimiter and use the default, the string gets split only once with a new line. How can I split my string with multiple new line separated entries?

Answer 1

The default delimiter for apex_string.split is LF (line feed) so one way to do this is to replace the CR+LF combos with a single LF before passing it to split:

select * from "MyTable" a 
where NAME in (select * from 
    TABLE(apex_string.split(
        replace(:P23_TEXTAREA, chr(13)||chr(10), chr(10))
    ))
)