apex_string.split new line as delimiter-CodePudding

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?

CodePudding user response：

You can try the CHR function:

SELECT *
FROM   TABLE(apex_string.split(:P23_TEXTAREA, CHR(10)))

or a string literal containing a newline:

SELECT *
FROM   TABLE(apex_string.split(:P23_TEXTAREA, '
'))

Your :P23_TEXTAREA value is: 'TRI_1000'||CHR(13)||CHR(10)||'TRI_10' which, if you split it on CHR(10) (LF) will give you the values 'TRI_1000'||CHR(13) and 'TRI_10' and the second will probably match but the first will not due to the trailing carriage return (CR) character.

It appears you need to either trim the result or split on CR/LF (or an optional CR) rather than just LF:

SELECT *
FROM   "MyTable"
WHERE  name IN (SELECT * 
                FROM   TABLE(apex_string.split(:P23_TEXTAREA, '\r?\n'))
               )

or:

SELECT *
FROM   "MyTable"
WHERE  name IN (SELECT RTRIM(COLUMN_VALUE, CHR(13)) 
                FROM   TABLE(apex_string.split(:P23_TEXTAREA, CHR(10)))
               )