create proc char_manage
@chars varchar(200)
as
create table #aa(no int identity(1,1),qty decimal(18,2),date1 varchar(10))declare @sql varchar(200),@left varchar(100),@right varchar(200),@qty decimal(18,2),@sqls varchar(1000)
select @sql=right(@chars,len(@chars)-charindex('[',@chars))
,@sql=left(@sql,len(@sql)-1)
while charindex('(',@sql)>0
begin
select @left=left(@sql,charindex('(',@sql)-1)
,@right=right(@sql,len(@sql)-charindex('(',@sql))
,@sql=right(@sql,len(@right)-charindex(';',@right))
,@qty=convert(decimal(18,2),left(@right,charindex(')',@right)-1))
,@sqls=replace(@left,';',''' union all select '+cast(@qty as varchar(10))+',''')
,@sqls='select '+cast(@qty as varchar(10))+','''+@sqls+''''
exec('insert #aa '+@sqls)
end
select * from #aa
go
create table #bb(no int,qty decimal(18,2),date varchar(10))
declare @chars varchar(200)set @chars='2/-2(A3AF-119)[11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)]'
insert into #bb exec char_manage @chars --这个会出错
select * from #bb
@chars varchar(200)
as
create table #aa(no int identity(1,1),qty decimal(18,2),date1 varchar(10))declare @sql varchar(200),@left varchar(100),@right varchar(200),@qty decimal(18,2),@sqls varchar(1000)
select @sql=right(@chars,len(@chars)-charindex('[',@chars))
,@sql=left(@sql,len(@sql)-1)
while charindex('(',@sql)>0
begin
select @left=left(@sql,charindex('(',@sql)-1)
,@right=right(@sql,len(@sql)-charindex('(',@sql))
,@sql=right(@sql,len(@right)-charindex(';',@right))
,@qty=convert(decimal(18,2),left(@right,charindex(')',@right)-1))
,@sqls=replace(@left,';',''' union all select '+cast(@qty as varchar(10))+',''')
,@sqls='select '+cast(@qty as varchar(10))+','''+@sqls+''''
exec('insert #aa '+@sqls)
end
select * from #aa
go
create table #bb(no int,qty decimal(18,2),date varchar(10))
declare @chars varchar(200)set @chars='2/-2(A3AF-119)[11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)]'
insert into #bb exec char_manage @chars --这个会出错
select * from #bb
alter proc char_manage
@chars varchar(200)
as--declare @aa table (no int identity(1,1),qty decimal(18,2),date1 varchar(10))
--create table #aa(no int identity(1,1),qty decimal(18,2),date1 varchar(10))declare @sql varchar(200),@left varchar(100),@right varchar(200),@qty decimal(18,2),@sqls varchar(1000)
set @sql=right(@chars,len(@chars)-charindex('[',@chars))
set @sql=left(@sql,len(@sql)-1)
set @sqls = ''
--select @sql
-----set @sql='11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)'
while charindex('(',@sql)>0
begin
set @sqls = (case when @sqls = '' then '' else @sqls + ' union all ' end)
set @left=left(@sql,charindex('(',@sql)-1)
--select @left
set @right=right(@sql,len(@sql)-charindex('(',@sql))
--select @right
set @sql=right(@sql,len(@right)-charindex(';',@right))
--select @sql
set @qty=convert(decimal(18,2),left(@right,charindex(')',@right)-1))
--select @qty
set @sqls=replace(@left,';',''' union all select '+cast(@qty as varchar(10))+',''')
--select @sqls
set @sqls='select '+cast(@qty as varchar(10))+','''+@sqls+''''
--select @sqls
-- insert @aa exec(@sqls)
end
-- select * from @aa
exec(@sqls)
gocreate table #bb(no int identity,qty decimal(18,2),date varchar(10))
declare @chars varchar(200)set @chars='2/-2(A3AF-119)[11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)]'
exec char_manage @chars
insert into #bb(qty,date) exec char_manage @chars --这个会出错
select * from #bb
alter proc char_manage
@chars varchar(200)
as
declare @sql varchar(200),@left varchar(100),@right varchar(200),@qty decimal(18,2),@sqls varchar(1000)
set @sql=right(@chars,len(@chars)-charindex('[',@chars))
set @sql=left(@sql,len(@sql)-1)
set @sqls = ''
while charindex('(',@sql)>0
begin
set @sqls = (case when @sqls = '' then '' else @sqls + ' union all ' end)
set @left=left(@sql,charindex('(',@sql)-1)
set @right=right(@sql,len(@sql)-charindex('(',@sql))
set @sql=right(@sql,len(@right)-charindex(';',@right))
set @qty=convert(decimal(18,2),left(@right,charindex(')',@right)-1))
set @sqls=replace(@left,';',''' union all select '+cast(@qty as varchar(10))+',''')
set @sqls='select '+cast(@qty as varchar(10))+','''+@sqls+''''
end
exec(@sqls)
gocreate table #bb(no int identity,qty decimal(18,2),date varchar(10))
declare @chars varchar(200)set @chars='2/-2(A3AF-119)[11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)]'
exec char_manage @chars
insert into #bb(qty,date) exec char_manage @chars --这个会出错
select * from #bb
no qty date
----------- -------------------- ----------
1 300.00 18/11
2 300.00 20/11
3 300.00 23/11
4 300.00 18/11
5 300.00 20/11
6 300.00 23/11(所影响的行数为 6 行)
drop table #bb
go
create table #bb(no int identity,qty decimal(18,2),date varchar(10))
declare @chars varchar(200)set @chars='2/-2(A3AF-119)[11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)]'
exec char_manage @chars
insert into #bb(qty,date) exec char_manage @chars --这个会出错
select * from #bb
------- -----
300.00 18/11
300.00 20/11
300.00 23/11(所影响的行数为 3 行)
(所影响的行数为 3 行)no qty date
----------- -------------------- ----------
1 300.00 18/11
2 300.00 20/11
3 300.00 23/11(所影响的行数为 3 行)
@chars varchar(200)
as
create table #aa(no int identity(1,1),qty decimal(18,2),date1 varchar(10))declare @sql varchar(200),@left varchar(100),@right varchar(200),@qty decimal(18,2),@sqls varchar(1000)
set @sql=right(@chars,len(@chars)-charindex('[',@chars))
set @sql=left(@sql,len(@sql)-1)
--select @sql
-----set @sql='11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)'
while charindex('(',@sql)>0
begin
set @left=left(@sql,charindex('(',@sql)-1)
--select @left
select @right=right(@sql,len(@sql)-charindex('(',@sql))
--select @right
select @sql=right(@sql,len(@right)-charindex(';',@right))
--select @sql
select @qty=convert(decimal(18,2),left(@right,charindex(')',@right)-1))
--select @qty
select @sqls=replace(@left,';',''' union all select '+cast(@qty as varchar(10))+',''')
--select @sqls
select @sqls='insert #aa select '+cast(@qty as varchar(10))+','''+@sqls+'''' --改这里
--select @sqls
exec(@sqls) --和这里
end
select * from #aa
drop table #aa
go
create proc char_manage
@chars varchar(200),
@resql varchar(8000)
as
create table #aa(no int identity(1,1),qty decimal(18,2),date1 varchar(10))declare @sql nvarchar(400),@left varchar(100),@right varchar(200),@qty decimal(18,2),@sqls nvarchar(2000)
set @sql=right(@chars,len(@chars)-charindex('[',@chars))
set @sql=left(@sql,len(@sql)-1)
set @resql=''
while charindex('(',@sql)>0
begin
set @left=left(@sql,charindex('(',@sql)-1)
--select @left
select @right=right(@sql,len(@sql)-charindex('(',@sql))
--select @right
select @sql=right(@sql,len(@right)-charindex(';',@right))
--select @sql
select @qty=convert(decimal(18,2),left(@right,charindex(')',@right)-1))
--select @qty
select @sqls=replace(@left,';',''' union all select '+cast(@qty as varchar(10))+',''')
--select @sqls
select @sqls='select '+cast(@qty as varchar(10))+','''+@sqls+''''
--select @sqls
select @resql=@resql+' union all '+@sqls
endset @resql=substring(@resql,10,8000)go??
no qty date1
----------- -------------------- ----------
1 100.00 11/11
2 100.00 12/11
3 100.00 15/11
4 200.00 15/11
5 200.00 17/11
6 300.00 18/11
7 300.00 20/11
8 300.00 23/11--邹建的可以,多谢邹建,马可!
@chars varchar(200)
as
declare @sql varchar(200),@left varchar(100),@right varchar(200),@qty decimal(18,2),@sqls varchar(1000),@sqls2 varchar(8000)
select @sql=right(@chars,len(@chars)-charindex('[',@chars)), @sql=left(@sql,len(@sql)-1),@sqls2=''
while charindex('(',@sql)>0
begin
set @left=left(@sql,charindex('(',@sql)-1)
--select @left
select @right=right(@sql,len(@sql)-charindex('(',@sql))
--select @right
select @sql=right(@sql,len(@right)-charindex(';',@right))
--select @sql
select @qty=convert(decimal(18,2),left(@right,charindex(')',@right)-1))
--select @qty
select @sqls=replace(@left,';',''' union all select '+cast(@qty as varchar(10))+',''')
--select @sqls
select @sqls='select '+cast(@qty as varchar(10))+','''+@sqls+''''
set @sqls2=@sqls2+' union all '+@sqls
end
set @sqls2=substring(@sqls2,12,8000)
print @sqls2
exec(@sqls2)
gocreate table #bb(no int identity,qty decimal(18,2),date varchar(10))
declare @chars varchar(200)set @chars='2/-2(A3AF-119)[11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)]'
--exec char_manage @chars
insert into #bb(qty,date) exec char_manage @chars --这个会出错
select * from #bbdrop table #bb
----------- -------------------- ----------
1 100.00 11/11
2 100.00 12/11
3 100.00 15/11
4 200.00 15/11
5 200.00 17/11
6 300.00 18/11
7 300.00 20/11
8 300.00 23/11(所影响的行数为 8 行)
GO
SET ANSI_NULLS OFF
GO
create function char_manage
(@chars varchar(200))
returns @aa table (no int identity(1,1),qty decimal(18,2),date1 varchar(10) )
as
begin declare @char varchar(200),@date varchar(10),@qty decimal(18,2),@tmp varchar(200)
set @char=right(@chars,len(@chars)-charindex('[',@chars))
set @char=left(@char,len(@char)-1)+';'
-----set @sql='11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)'
while @char<>''
begin
set @tmp=left(@char,charindex(')',@char))
--set @qty=
set @qty=right(left(@tmp,len(@tmp)-1),len(left(@tmp,len(@tmp)-1))-charindex('(',left(@tmp,len(@tmp)-1)))
set @tmp=left(@tmp,len(@tmp)-charindex('(',@tmp))
while @tmp<>''
begin
if charindex(';',@tmp)<>0
begin
set @date=left(@tmp,charindex(';',@tmp)-1)
set @tmp=right(@tmp,charindex(';',@tmp)-1)
end
else
begin
set @date=@tmp
set @tmp=''
end
insert into @aa (Qty,date1) values (@qty,@date)
end
set @char=right(@char,len(@char)-charindex(';',@char) )
end
return endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOselect * from dbo.char_manage('2/-2(A3AF-119)[11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)]')完美了20分钟
if exists (select * from sysobjects where name='char_manage') drop function char_manage
GO
create function char_manage
(@chars varchar(200))
returns @aa table (no int identity(1,1),qty decimal(18,2),date1 varchar(10) )
as
begin
declare @char varchar(200),@date varchar(10),@qty decimal(18,2),@tmp varchar(200)
set @char=right(@chars,len(@chars)-charindex('[',@chars))
set @char=left(@char,len(@char)-1)+';'
-----set @sql='11/11;12/11;15/11(100);15/11;17/11(200);18/11;20/11;23/11(300)'while @char<>''
begin
set @tmp=left(@char,charindex(')',@char))
--set @qty=
set @qty=right(left(@tmp,len(@tmp)-1),len(left(@tmp,len(@tmp)-1))-charindex('(',left(@tmp,len(@tmp)-1)))
set @tmp=left(@tmp,charindex('(',@tmp)-1)+';'
while @tmp<>''
begin
set @date=left(@tmp,charindex(';',@tmp)-1)
set @tmp=right(@tmp,len(@tmp)-charindex(';',@tmp))
set @char=right(@char,len(@char)-charindex(';',@char) )
insert into @aa (Qty,date1) values (@qty,@date)
end
end
return
end