oracle字符串转多行

with t as (SELECT 'Newyork;London;Paris' city_names FROM dual

           UNION ALL SELECT 'Sydney;Singapore' FROM dual

           UNION ALL select 'California' from dual)

    ,t2 as (select row_number() over (order by 1) as id, city_names from t)

SELECT t2.id, regexp_substr(city_names,'[^;]+',1,LEVEL) AS val

FROM t2

CONNECT BY LEVEL <= regexp_count(city_names,';') + 1

   and prior id = id

   and prior sys_guid() is not null;