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?
-
It also works with clob...Tomasz– Tomasz2022-03-07 22:58:29 +00:00Commented Mar 7, 2022 at 22:58
Add a comment
|
2 Answers
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;
1 Comment
MrE
actually, this will likely bring issues, because APEX_STRING.SPLIT results in a nested table, and there is guarantee that the order will be the same when you run it multiple times. You should rather use the regxp_substr method
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
