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+','''+replace(@Dep_Code,'/',''')   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

发表于 @ 2009年04月07日　16:13:00 | 评论( loading... ) | 编辑| 举报| 收藏

旧一篇:从SQL Server中导入/导出 Excel 的基本方法  | 新一篇:SQL查询分析中使用net命令问题