表A
FNAME DETAILSTR
ABC 36.5*5+37.5+37*2+45.2+36
转换成表B
FNAME FNUM
ABC 36.5
ABC 36.5
ABC 36.5
ABC 36.5
ABC 36.5
ABC 37.5
ABC 37
ABC 37
ABC 45.2
ABC 36
------------------------
根据字符串里的数量转换成表B的形式,在sql2000中该怎么处理
FNAME DETAILSTR
ABC 36.5*5+37.5+37*2+45.2+36
转换成表B
FNAME FNUM
ABC 36.5
ABC 36.5
ABC 36.5
ABC 36.5
ABC 36.5
ABC 37.5
ABC 37
ABC 37
ABC 45.2
ABC 36
------------------------
根据字符串里的数量转换成表B的形式,在sql2000中该怎么处理
go
create table [tb]([FNAME] varchar(3),[DETAILSTR] varchar(24))
insert [tb]
select 'ABC','36.5*5+37.5+37*2+45.2+36'
goSELECT A.FNAME,A.FNUM
FROM
(
select
fname,
fnum=left(fnum+'*',charindex('*',fnum+'*')-1),
qty=case when charindex('*',fnum)>0 then right(fnum,len(fnum)-charindex('*',fnum)) else 1 end
from
(
select
a.FNAME,
FNUM=substring(a.DETAILSTR,b.number,charindex('+',a.DETAILSTR+'+',b.number)-b.number)
from tb a,master..spt_values b
where b.type='P' and charindex('+','+'+a.DETAILSTR,b.number)=b.number
) t
) A
JOIN MASTER..SPT_VALUES B ON B.TYPE='P' AND A.QTY>B.NUMBER/**
FNAME FNUM
----- -------------------------
ABC 36.5
ABC 36.5
ABC 36.5
ABC 36.5
ABC 36.5
ABC 37.5
ABC 37
ABC 37
ABC 45.2
ABC 36(10 行受影响)
**/
create function [dbo].[m_split](@c varchar(2000),@split1 varchar(2),@split2 varchar(2),@name varchar(5))
returns @t table(fname varchar(5),col varchar(200))
as
begin
declare @i int
while (charindex(@split1,@c)<>0 )
begin
if(charindex(@split2,@c)<>0 and charindex(@split2,@c)<charindex(@split1,@c))
begin
set @i=cast(replace(left(@c,charindex(@split1,@c)-1),left(@c,charindex(@split2,@c)),'') as int)
while(@i>0)
begin
insert @t(fname,col) values (@name,substring(@c,1,charindex(@split2,@c)-1))
set @i=@i-1
end
set @c = stuff(@c,1,charindex(@split1,@c),'')
end
else
begin
insert @t(fname,col) values (@name,substring(@c,1,charindex(@split1,@c)-1))
set @c = stuff(@c,1,charindex(@split1,@c),'')
end
end
if(charindex(@split2,@c)<>0)
begin
set @i=cast(replace(@c,left(@c,charindex(@split2,@c)),'') as int)
while(@i>0)
begin
insert @t(fname,col) values (@name,substring(@c,1,charindex(@split2,@c)-1))
set @i=@i-1
end
end
else
insert @t(fname,col) values (@name,@c)
return
endif object_id('[tb]') is not null drop table [tb]
go
create table [tb]([FNAME] varchar(3),[DETAILSTR] varchar(24))
insert [tb]
select 'ABC','36.5*5+37.5+37*2+45.2+36'
go
declare @sql varchar(40),@name varchar(5)
select @sql=DETAILSTR,@name=FNAME from tb
select * from dbo.m_split(@sql,'+','*',@name)fname col
ABC 36.5
ABC 36.5
ABC 36.5
ABC 36.5
ABC 36.5
ABC 37.5
ABC 37
ABC 37
ABC 45.2
ABC 36