Question

sql语句依据特殊字符截取字符串获取数据

现有表格A数据如下：
id num
-----------------
1 121,34,56
2 12
3 23,09

想通过sql语句中字符串截取得数据，并插入表B中，得到表的最终形式如下：
id num
-------------------
1 121
1 34
1 56
2 12
3 23
3 09

sql语句该怎么写？

Answer 1

CREATE TABLE [#tmp] (
[数字] [varchar] (35) COLLATE Chinese_PRC_CI_AS NULL ,
) ON [PRIMARY]
declare @Len int
set @Len=1
declare @hen int
set @hen=1
while @hen>0
begin
SELECT @hen=CHARINDEX('-', substring('20-45-122-25-1',@Len,Len('20-45-122-25-1' )) )

IF @hen=0 AND CHARINDEX('-',REVERSE('20-45-122-25-1'))>1
BEGIN

insert into #tmp ([数字])
(SELECT REVERSE(SUBSTRING (REVERSE('20-45-122-25-1'),1,CHARINDEX('-',REVERSE('20-45-122-25-1')))))
END
ELSE
insert into #tmp ([数字])
(select substring('20-45-122-25-1',@Len,@hen ))
--select substring(substring('20-45-122-25-1',@Len,Len('20-45-122-25-1' )),1, @hen+1))
set @Len=@Len+@hen

END
select REPLACE([数字],'-','') from #tmp
drop table #tmp