日期:2014-05-17 浏览次数:20584 次
if 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 SELECT 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 end if 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