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