How to split a string seperated by space in oracle and add each word to a different row in Oracle 11g?

Question

I am trying to split a string and add each word to a separate rows.

with data as (
  select 100 as id, 'python java' as src from dual
)
 select id, level as lvl,
        regexp_substr( src || '" "' , '([[:space:]0-9/:])', 1, level, null, 1 ) as token
   from data
connect by level <= regexp_count( src || '" "' , '([[:space:]0-9/:])' )
       and prior id = id
       and prior sys_guid() is not null
;

I am expecting python and java in a separate rows.

Answer 1

Your regex seems a bit confused. I think you want something more like:

regexp_substr( src , '(.*?)([[:space:]0-9/:]|$)', 1, level, null, 1 )

db<>fiddle

Answer 2

You can use combination of regexp_substr() and regexp_count() with connect by level<... expression :

with t as
(
  select 100 as id, 'python java' as src from dual
)
 select id, level as lvl, regexp_substr(src, '[^[:space:]]+',1,level) as token
   from t 
connect by level<= regexp_count(src,'[[:space:]]')+1
    and prior id = id
    and prior sys_guid() is not null;

Demo