带分隔符字串转表格语句

SqlServer

--单语句

with t as (
select '1,2,3,4,5,6,7,8,9999' c
union all
select right(c,len(c)-CHARINDEX(',',c)) from t where CHARINDEX(',',c)>0
),
t1 as (
select c+',' c from t
)
select left(c,charindex(',',c)-1) from t1 OPTION (MAXRECURSION 0)

---

--块代码

begin

declare @src varchar(1024) = '1,2,3,4,5,6,7,8,9999';
declare @div varchar(1) = ',';

with t as (
select @src c
union all
select right(c,len(c)-CHARINDEX(@div,c)) from t where CHARINDEX(@div,c)>0
),
t1 as (
select c+@div c from t
)
select left(c,charindex(@div,c)-1) from t1 OPTION (MAXRECURSION 0);

end

 Oracle

SELECT REGEXP_SUBSTR('1,2,3,4,5', '[^,]+', 1, LEVEL) ID
  FROM DUAL
CONNECT BY REGEXP_SUBSTR('1,2,3,4,5', '[^,]+', 1, LEVEL) IS NOT NULL