表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中该怎么处理
------解决方案--------------------
- SQL code
if object_id('[tb]') is not null drop table [tb]gocreate 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.FNUMFROM ( 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 ) AJOIN MASTER..SPT_VALUES B ON B.TYPE='P' AND A.QTY>B.NUMBER/**FNAME FNUM----- -------------------------ABC 36.5ABC 36.5ABC 36.5ABC 36.5ABC 36.5ABC 37.5ABC 37ABC 37ABC 45.2ABC 36(10 行受影响)**/
------解决方案--------------------
早上在看叶子的博客，改了下
- SQL code
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 @[email protected] 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 @[email protected] end end else insert @t(fname,col) values (@name,@c) return endif object_id('[tb]') is not null drop table [tb]gocreate table [tb]([FNAME] varchar(3),[DETAILSTR] varchar(24))insert [tb]select 'ABC','36.5*5+37.5+37*2+45.2+36'godeclare @sql varchar(40),@name varchar(5)select @sql=DETAILSTR,@name=FNAME from tbselect * from dbo.m_split(@sql,'+','*',@name)fname colABC 36.5ABC 36.5ABC 36.5ABC 36.5ABC 36.5ABC 37.5ABC 37ABC 37ABC 45.2ABC 36