Is there any function which checks a mathematical expression and returns a valid number in SQL server? -
i have below code im using isnumeric function check validity of expression math in sql.
but if use isnumeric function here returns 0 both 2 conditons listed below. need function or method identify valid mathematical expression.
is there alternative 1 ?
begin declare @rcnt varchar(100) declare @ncnt varchar(100) --condition1 set @rcnt = '(5918800.000 / 4963400.000) * (slaughter(1023))' --condition2 set @ncnt = '(5997200.000 / 5059900.000) * (400000.000)' select isnumeric(@rcnt) validittcheck select isnumeric(@ncnt ) validittcheck end
isnumeric() check variable valid numeric datatype, not result of evaluation of string expression valid numeric value. values became non-numeric first '(' evaluated.
the way know desired result use dynamic sql. here example, notice put try catch in there because slaughter() function not exist , exception raised, return 0 not number:
declare @rcnt nvarchar(100) declare @ncnt nvarchar(100) --condition1 set @rcnt = 'select @result=isnumeric( (5918800.000 / 4963400.000) * (slaughter(1023)) )' --condition2 set @ncnt = 'select @result=isnumeric( (5997200.000 / 5059900.000) * (400000.000) )' declare @resultvalue1 int=0 declare @resultvalue2 int=0 declare @resultparam nvarchar(100)= '@result int output' begin try exec sp_executesql @rcnt, @resultparam, @result=@resultvalue1 output end try begin catch end catch begin try exec sp_executesql @ncnt, @resultparam, @result=@resultvalue2 output end try begin catch end catch select @resultvalue1 select @resultvalue2 