表中有字符型字段,存放诸如 5*8 这样的字符串,如何计算出值 40 来?
--------------------------------------------
有一列,存的是字符串,放的是公式
如:
4*9
23*90
345/456我想通过这列中的公式,计算出值
如
4*9 得出 36
--------------------------------------------
有一列,存的是字符串,放的是公式
如:
4*9
23*90
345/456我想通过这列中的公式,计算出值
如
4*9 得出 36
declare @sql nvarchar(200)
declare @result numeric(18,4)
declare @col_text nvarchar(200)
set @col_text = '4*9'
set @sql ='select @result = ' + @col_text
exec sp_executesql @sql,N'@result numeric(18,4) output',@result output
select @result
go
create table [tb]([col] varchar(7))
insert [tb]
select '4*9' union all
select '23*90' union all
select '345/456'
godeclare @sql varchar(8000)
select
@sql=isnull(@sql+' union ','')+'select '''+col+''' as col,'+col+' as val from tb'
from tb
--print @sql
exec (@sql)/**
col val
------- -----------
23*90 2070
345/456 0
4*9 36(3 行受影响)
**/
----------- ---------------------------------------
1 5.00
2 6.00
3 0.20
4 0.00
5 13.00
6 9.00(6 個資料列受到影響)*/--方法3 函數if object_id('f_calc')is not null drop function f_calcgocreate function f_calc(@str varchar(1000)--要计 oa的表达 |?)returns sql_variantasbegindeclare @re sql_variantdeclare @err int,@src varchar(255),@desc varchar(255)declare @obj intexec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj outif @err<>0 goto lb_errexec @err=sp_oasetproperty @obj,'Language','vbscript'if @err<>0 goto lb_errexec @err=sp_oamethod @obj,'Eval',@re out,@strif @err=0 return(@re)lb_err:exec sp_oageterrorinfo NULL, @src out, @desc out declare @errb varbinary(4),@s varchar(20)set @errb=cast(@err as varbinary(4))exec master..xp_varbintohexstr @errb,@s outreturn(N'错誤号 '+@s+char(13)+N'错誤源: '+@src+char(13)+N'错误描述: '+@desc)endgo--以上方法要啟用OLE Automation Procedures 方法如下sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1;GORECONFIGURE;GOselect ID, dbo.f_calc(val)valfrom tb/*ID val----------- --------------1 52 63 .24 .25 136 9(6 個資料列受到影響)*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wufeng4552/archive/2009/11/25/4868138.aspx
drop table tb
create table tb(gongshi varchar(100))select * from tb
insert tb
select '4*9'
union all
select '23*90'
union all
select '345/456'--以下是+、-、*、/其它可以自己添加
select case when patindex('%*%',gongshi)!=0 then cast(substring(gongshi,1,patindex('%*%',gongshi)-1) as float)*cast(substring(gongshi,patindex('%*%',gongshi)+1,len(gongshi)-patindex('%*%',gongshi)) as float)
when patindex('%/%',gongshi)!=0 then cast(substring(gongshi,1,patindex('%/%',gongshi)-1) as float)/cast(substring(gongshi,patindex('%/%',gongshi)+1,len(gongshi)-patindex('%/%',gongshi)) as float)
when patindex('%+%',gongshi)!=0 then cast(substring(gongshi,1,patindex('%+%',gongshi)-1) as float)+cast(substring(gongshi,patindex('%+%',gongshi)+1,len(gongshi)-patindex('%+%',gongshi)) as float)
when patindex('%-%',gongshi)!=0 then cast(substring(gongshi,1,patindex('%-%',gongshi)-1) as float)-cast(substring(gongshi,patindex('%-%',gongshi)+1,len(gongshi)-patindex('%-%',gongshi)) as float)
else 0 end from tb
create table #tb (ID int,val nvarchar(200))
insert into #tb
select 1,'1*5' union all
select 2,'1+5' union all
select 3,'1.0/5' union all
select 4,'1/5' union all
select 5,'2*5+3' union all
select 6,'(8-5)*3'declare @sql nvarchar(4000)
set @sql=''
select @sql=@sql+' union all select '''+val+''' as val,cast('+val+' as dec(18,2)) as 结果' from #tb
set @sql=stuff(@sql,1,10,'')
exec(@sql)