用SQL游标将1列中的数据分解成3列


SELECT Number,ApplyYear,CompanyNo,ContractNo FROM [Contract].[dbo].[Apply]
update dbo.Apply set ApplyYear = '',CompanyNo = '',ContractNo='' where Number != ''

declare  cursor1  cursor  for  
select Id,Number from [Contract].[dbo].[Apply] where Number != ''

declare  @id uniqueidentifier;
declare  @number nchar(20);  
declare  @year  nchar(10);
declare  @companyno  nchar(10);
declare  @contractno char(3);
 
open cursor1;
fetch next from cursor1 into @id,@number;

while @@FETCH_STATUS = 0
begin     
     select @year = [Value] from [dbo].[SplitString2](@number, '-', 1,1)
     select @companyno = [Value] from [dbo].[SplitString2](@number, '-', 1,2)
     select @contractno = [Value] from [dbo].[SplitString2](@number, '-', 1,3)  
     update [Contract].[dbo].[Apply]
     set ApplyYear = @year, CompanyNo = @companyno, ContractNo = CAST(@contractno as int) where  Id = @id;
     fetch next from cursor1  into @id, @number;
end

close cursor1;
deallocate cursor1;

------------------------
Number      ApplyYear    CompanyNo    ContractNo
2012-03-108    2012        03           108
2012-09-150    2012        09        150
2012-06-172    2012        06        172
------------------------

--sql中char/varchar 如何转化为int类型?
--cast(字段   as   int) 或者convert(int,字段)

posted @ 2012-06-13 09:33  宁静.致远  阅读(287)  评论(0编辑  收藏  举报