How to use apex_string.split?

Question

I'm trying to split a string : OK#15#78 by #

I would like to get the first part of the string : Ok

I tried the following queries but it's not working :

select apex_string.split('OK#15#78','#')[0] from dual;

What can I try next?

Answer 1

You could use TABLE and rownum:

SELECT val
FROM (Select rownum AS rn, column_value AS val
     FROM TABLE(apex_string.split('OK#15#78','#')))
WHERE rn = 1;

Answer 2

ORIGNAL POST:

Even simpler:

SELECT COLUMN_VALUE val, ROWNUM
FROM apex_string.split('OK#15#78','#')
WHERE ROWNUM = 1;

Or maybe even faster:

SELECT COLUMN_VALUE val
FROM apex_string.split('OK#15#78','#')
FETCH FIRST 1 ROWS ONLY

REVISED ANSWER

There is a caveat here: APEX_STRING.SPLIT results in a nested table, and there are no guarantees about the order of the returned items, so this actually doesn't work.

You should use the regxp_substr method

SELECT val c
FROM 
(
   (SELECT regexp_substr('OK#15#78','[^#]+', 1, level) 
    FROM DUAL
    CONNECT BY regexp_substr(, '[^#]+', 1, level) IS NOT NULL)
)
FETCH FIRST 1 ROWS ONLY