大概思路
Decalre @tmp varchar(100),
@val varchar(10),
@pos int
Select @tmp = @aa
While CharIndex(' ',@tmp)>0
Begin
Select @pos = CharIndex(' ',@tmp)
Select @val = Sunstring(@tmp,1,@pos-1)
Print @val
Select @tmp = Substring(@tmp,@pos+1,len(@tmp)-@pos)
End
Decalre @tmp varchar(100),
@val varchar(10),
@pos int
Select @tmp = @aa
While CharIndex(' ',@tmp)>0
Begin
Select @pos = CharIndex(' ',@tmp)
Select @val = Sunstring(@tmp,1,@pos-1)
Print @val
Select @tmp = Substring(@tmp,@pos+1,len(@tmp)-@pos)
End
Decalre @tmp varchar(100),
@val varchar(10),
@pos int
Select @tmp = @aa
While CharIndex(' ',@tmp)>0
Begin
Select @pos = CharIndex(' ',@tmp)
Select @val = Sunstring(@tmp,1,@pos-1)
Print @val
Select @tmp = Substring(@tmp,@pos+1,len(@tmp)-@pos)
End
Print @tmp
@val varchar(100),
@pos int
Select @tmp = @aa
While CharIndex('@tmp')>0
Begin
Select @pos = CharIndex('@tmp')
Select @val = Sunstring(@tmp,1,@pos-1)
Print @val
Select @tmp = Substring(@tmp,@pos+1,len(@tmp)-@pos)
End
Print @tmp
declare @aa varchar(20) , @sql varchar(1000)
set @aa = '1 2 3 4 5 6 7 8 9 0'
create table #t1(item varchar(20))
select @sql = 'insert into #t1 values('''+REPLACE(@aa,' ',''') insert into #t1 values(''')+''')'
exec (@sql)
select * from #t1
……
drop table #t1
go
@str varchar(300),
@ret table(s1 varchar(30)) OUTPUT
AS
BEGIN
set @str=rtrim(ltrim(@str))
while CharIndex(' ',@str)>0
begin
insert @ret values(left(@str,CharIndex(' ',@str)-1))
set @str=ltrim(right(@str,len(@str)-CharIndex(' ',@str)))
end
insert @ret values(@str)
RETURN
END
@str varchar(300),
@ret table(s1 varchar(30)) OUTPUT
AS
BEGIN
set @str=rtrim(ltrim(@str))
while CharIndex(' ',@str)>0
begin
exec('insert '+@ret+' values(left(@str,CharIndex(' ',@str)-1))')
set @str=ltrim(right(@str,len(@str)-CharIndex(' ',@str)))
end
exec('insert '+@ret+' values(@str)')
RETURN
END
@str varchar(300),
@ret table(s1 varchar(30)) OUTPUT
AS
BEGIN
set @str=rtrim(ltrim(@str))
while CharIndex(' ',@str)>0
begin
exec('insert '+@ret+' values(left('+@str+',CharIndex('' '','+@str+')-1))')
set @str=ltrim(right(@str,len(@str)-CharIndex(' ',@str)))
end
exec('insert '+@ret+' values('+@str+')')
RETURN
END
declare @aa varchar(20) , @sql varchar(1000)
set @aa = '1 2 3 4 5 6 7 8 9 0'
create table #t1(item varchar(20))
select @sql = 'insert into #t1 values('''+REPLACE(@aa,' ',''') insert into #t1 values(''')+''')'
exec (@sql)
select * from #t1
……
drop table #t1
go
create table #item(item varchar(20))
go
declare @a varchar(300)
declare @sql nvarchar(500)
set @a='1 2 3 4 5 6 7 8 9 0'
set @a=ltrim(rtrim(@a))
set @sql='insert into #item values('''+replace(@a,' ',''') insert into #item values(''')+''')'
exec(@sql)
select * from #item