declare xcursor cursor for select * from style
declare @name varchar(100),@coll int,@colf int,@start varchar(20),@length varchar(20)
declare @sql varchar(8000)
set @sql='select '
open xcursor
fetch xcursor into @name,@coll,@colf
while @@fetch_status=0
begin
select @start=cast(@coll as varchar(20))
select @length=cast(@colf-@coll as varchar(20))
select @sql=@sql+'substring(note,'+@start+','+@length+') as '+@name+','
fetch xcursor into @name,@coll,@colf
end
close xcursor
deallocate xcursor
select @sql=left(@sql,len(@sql)-1)+' from table1'
select @sql
exec(@sql)
declare @name varchar(100),@coll int,@colf int,@start varchar(20),@length varchar(20)
declare @sql varchar(8000)
set @sql='select '
open xcursor
fetch xcursor into @name,@coll,@colf
while @@fetch_status=0
begin
select @start=cast(@coll as varchar(20))
select @length=cast(@colf-@coll as varchar(20))
select @sql=@sql+'substring(note,'+@start+','+@length+') as '+@name+','
fetch xcursor into @name,@coll,@colf
end
close xcursor
deallocate xcursor
select @sql=left(@sql,len(@sql)-1)+' from table1'
select @sql
exec(@sql)
insert into style values( 'WELL', 0, 11)
insert into style values( 'T_ID', 12, 28)
insert into style values( 'C_ID', 29, 40)
go
create table table1 (note varchar(8000))
insert into table1 values('CF00-123 1285.0036 56703.265')
insert into table1 values('abc-123 2285.043 11123.000')
go
declare xcursor cursor for select * from style
declare @name varchar(100),@coll int,@colf int,@start varchar(20),@length varchar(20)
declare @sql varchar(8000)
set @sql='select '
open xcursor
fetch xcursor into @name,@coll,@colf
while @@fetch_status=0
begin
select @start=cast(@coll as varchar(20))
select @length=cast(@colf-@coll as varchar(20))
select @sql=@sql+'substring(note,'+@start+','+@length+') as '+@name+','
fetch xcursor into @name,@coll,@colf
end
close xcursor
deallocate xcursor
select @sql=left(@sql,len(@sql)-1)+' from table1'
exec(@sql)drop table style,table1
select substring('CF00-123 1285.0036 56703.265',29,11) as name from table1我是想要把table1中所有的数据分开然后:
insert into HT001(WELL,T_ID,C_ID)
values ('CF00-123',1285.0036,56703.265)你这个好象只取出来了一行中name一列的值。谢谢!帮我看一下。
比如我上面的例子table2中不是有两条记录吗?
不过要更正一点:
select @length=cast(@colf-@coll as varchar(20))改成
select @length=cast(@colf-@coll as varchar(20))+1
yoki(小马哥)你真是一个乐于助人的天才!