REGEXP_SUBSTR SPLIT Function

Question

i want to split this into 2019/GA/0000104

select REGEXP_SUBSTR('2019/0000015,2019/GA/0000104,2cdb376e-2966-4f24-9063-f4c6f31a6f35', '[^,]+')
from dual;

Output = 2019/GA/0000104

can u guys help?

Answer 1

It seems that you want to extract the second substring. If that's so, then you could use

• regexp_substr (result), or
• substr + inenter code herestr combination (result2)

---

SQL> with test (col) as
  2    (select '2019/0000015,2019/GA/0000104,2cdb376e-2966-4f24-9063-f4c6f31a6f35' from dual)
  3  select regexp_substr(col, '[^,]+', 1, 2) result,
  4         --
  5         substr(col, instr(col, ',', 1, 1) + 1,
  6                     instr(col, ',', 1, 2) - instr(col, ',', 1, 1) - 1
  7               ) result2
  8  from test;

RESULT          RESULT2
--------------- ---------------
2019/GA/0000104 2019/GA/0000104

SQL>

Answer 2

Try using REGEXP_SUBSTR with a capture group:

SELECT
    REGEXP_SUBSTR(input, ',(.*),', 1, 1, NULL, 1)
FROM yourTable;

Demo

Answer 3

This form of the regex returns the second occurrence of a string of characters that are followed by a comma or the end of the line. It returns the correct element if the first one should ever be NULL.

with tbl(str) as (
  select '2019/0000015,2019/GA/0000104,2cdb376e-2966-4f24-9063-f4c6f31a6f35' from dual
)
select regexp_substr(str, '(.*?)(,|$)', 1, 2, NULL, 1)
from tbl;