--方法1
SELECT
substring(a.[1],b.number,charindex(',',a.[1]+',',b.number)-b.number)[1]
,[2]
from [TB] a
join master..spt_values b on b.type='P'
where charindex(',',','+a.[1],b.number)=b.number
--方法2
SELECT T2.V [1],T1.[2] FROM
(SELECT [2],CONVERT(XML,'<V>'+REPLACE([1],',','</V><V>')+'</V>')VS FROM TB)T1
CROSS APPLY(SELECT N.V.value('.','NVARCHAR(50)')V FROM T1.VS.nodes('//V')N(V))T2
SELECT
substring(a.[1],b.number,charindex(',',a.[1]+',',b.number)-b.number)[1]
,[2]
from [TB] a
join master..spt_values b on b.type='P'
where charindex(',',','+a.[1],b.number)=b.number
--方法2
SELECT T2.V [1],T1.[2] FROM
(SELECT [2],CONVERT(XML,'<V>'+REPLACE([1],',','</V><V>')+'</V>')VS FROM TB)T1
CROSS APPLY(SELECT N.V.value('.','NVARCHAR(50)')V FROM T1.VS.nodes('//V')N(V))T2
1 2
a h
b,c,d h
这样也能变成
1 2
a h
b h
c h
d h
吗?
select REGEXP_SUBSTR(A, '[^,]+', 1, LEVEL) A,B,C
from T
CONNECT BY LEVEL <= REGEXP_COUNT(A, '[^,]+')
and rowid= prior rowid
and prior dbms_random.value is not null;
写法1:
SELECT col1,REGEXP_SUBSTR(col1,'[^,]+',1,rn) col12,col2
FROM t0052,(SELECT LEVEL rn FROM DUAL
CONNECT BY LEVEL<=(SELECT MAX(length(trim(translate(col1,replace(col1,','),' '))))+1 FROM t0052))
WHERE REGEXP_SUBSTR(col1,'[^,]+',1,rn) IS NOT NULL 写法2:
select regexp_substr(col1,'[^,]+',1,level) col1,col2
from t0052
connect by level <= (length(col1)-length(regexp_replace(col1,'[^,]+','')))
and rowid= prior rowid
and prior dbms_random.value is not null; 这两种写法都比较复杂,其中使用了Oracle的一些技巧(如为了避免循环错误使用的prior dbms_random.value is not null),对于使用者有一定难度。
这种情况还可以试试润乾免费版的集算器来做,集算脚本写成这样:
这个脚本要简单且易理解得多,其中A2即所得结果集:
集算器提供JDBC接口,可以像数据库一样嵌入到应用程序中,用起来很简单。
from dual
connect by
rownum<(length(regexp_replace('李四,张三,王五,赵六','[^,]',''))+2)