使用 REGEXP_SUBSTR( ,,,,)
例子:
SELECT REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL, 'i') AS ID ,
REGEXP_SUBSTR('1,2,3,4', '[^,]+', 1, LEVEL, 'i') AS TEXTFROM DUAL CONNECT BY LEVEL<= (LENGTH('A,B,C,D')-LENGTH(REPLACE('A,B,C,D',',','')))+1
'A,B,C,D' :需要转换的字符
'[^,]+' 用正则表达式取逗号
1 从第一个位置开始
LEVEL: 匹配第几组
'i':不区分大小写 'c':区分大小写
CONNECT BY LEVEL<= (LENGTH('A,B,C,D')-LENGTH(REPLACE('A,B,C,D',',','')))+1 :算出level的值