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,字段)