sqlserver处理方法
建表及数据
create table table1 (content varchar(200))
insert into table1 values ('aa,bb,cc,dd,ee')
执行
WITH roy
AS (
SELECT
COl1 = CAST(LEFT(content, CHARINDEX(',', content + ',') - 1) AS NVARCHAR(100)) ,
Split = CAST(STUFF(content + ',', 1,
CHARINDEX(',', content + ','), '') AS NVARCHAR(100))
FROM table1
UNION ALL
SELECT
COl1 = CAST(LEFT(Split, CHARINDEX(',', Split) - 1) AS NVARCHAR(100)) ,
Split = CAST(STUFF(Split, 1, CHARINDEX(',', Split), '') AS NVARCHAR(100))
FROM Roy WHERE split > '' )
SELECT COl1 FROM roy
结果
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/0bd162d9f2d3572c63071ace8813632762d0c33f?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
追问麻烦问问有没有oracle的方法
追答必须有啊,表还是刚才的表
select c from
(with test as (select content c from table1)--要是表名不一样,你只改这个表名和字段名即可
select substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) - (instr(t.ca, ',', 1, c.lv) + 1)) AS c
from (select ',' || c || ',' AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')),0) AS cnt FROM test) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt)