create table #ie_Department_Manager(Empl_Index nvarchar(10),Dep_Code nvarchar(200))
insert into #ie_Department_Manager values('656','AS011')
insert into #ie_Department_Manager values('657','AS011/AS012/AS013/AS021/AS022/AS023')
insert into #ie_Department_Manager values('658','AS011/AS012/AS013/AS021/AS022/AS023/AC011/AC012/AC013/AC021/AC022/AC023')
create table #tmpPersonForDep(Empl_Index nvarchar(10),Dep_Code nvarchar(20))
create table #tmpPersonForDep2(Empl_Index nvarchar(10),Dep_Code nvarchar(20))
declare tmpManager cursor for
select empl_index,Dep_Code from #ie_Department_Manager
order by Dep_Code
declare @Empl_Index nvarchar(10),@Dep_Code nvarchar(200),@position int,@index int
open tmpManager
fetch next from tmpManager
into @Empl_Index,@Dep_Code
while @@FETCH_STATUS = 0
begin
--法一:Replace命令
declare @comm nvarchar(4000)
set @comm='insert into #tmpPersonForDep values ('+@Empl_Index+',''')+''')'
--print @Empl_Index+'~'+@Dep_Code
--print @comm
execute (@comm)
--法二:CharIndex命令
set @position=charindex('/',@Dep_Code)
if right(@Dep_Code,1)<>'/'
begin
set @Dep_Code=@Dep_Code+'/'
set @position=charindex('/',@Dep_Code)
end
set @index=1
while @position>0
begin
-- select @empl_index,@dep_code,substring(@Dep_Code,@Index,@position-@Index)
insert into #tmpPersonForDep2 values(@empl_Index,substring(@Dep_Code,@Index,@position-@Index))
set @Index=@position+1
set @position=charindex('/',@Dep_Code,@Index)
end
fetch next from tmpManager
into @Empl_Index,@Dep_Code
end
select * from #tmpPersonForDep
select * from #tmpPersonForDep2
close tmpManager
deallocate tmpManager
drop table #tmpPersonForDep
drop table #tmpPersonForDep2
DROP TABLE #ie_Department_Manager