oracle列转行，拆分一个字段为多列

select  regexp_substr(cgi,'[^/]+',1,level,'i') as CGI,'4G' as ne_mode,t1.PROPERTY_NUM,city,county,PROPERTY_NAME
from  nwom.T_SITE_IMPORT_SENCE_HOOM t1 
where cgi is not null
connect by t1.PROPERTY_NUM = prior t1.PROPERTY_NUM
and prior dbms_random.value is not null
and level<= regexp_count(cgi,'[^/]+')

1、regexp_substr用法如下：
function REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier)
__srcstr ： 需要进行正则处理的字符串
__pattern ：进行匹配的正则表达式
__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）
__occurrence ：标识第几个匹配组，默认为1，当要匹配全部时，指定该位置的内容为level
__modifier ：模式（'i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’。）

例1：SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,1,'i') AS STR FROM DUAL;
例2：SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,3,'i') AS STR FROM DUAL
例3：

SELECT REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i') AS STR
FROM DUAL
CONNECT BY LEVEL <=
LENGTH('17,20,23') - LENGTH(REGEXP_REPLACE('17,20,23', ',', ''))+1;

例4：

SELECT REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i') AS STR
FROM DUAL
CONNECT BY LEVEL <=
regexp_count('17,20,23','[^,]+')

LENGTH(‘17,20,23’) - LENGTH(REGEXP_REPLACE(‘17,20,23’, ‘,’, ‘’))+1
相当于regexp_count(‘17,20,23’,‘[^,]+’)

扩展：
starrocks列转行语句：

select unnest as col from (
select ‘col1,col2’ as cols
) xx, unnest(split(cols, ‘,’))