February 23, 2009 at 4:17 am
Hi All,
A column contains the math expression in the form of string:
'(1-(982/33))*100'
Need a UDF to return the result.
SELECT udf_EVAL((1-(982/33))*100')
Returns: -2875
Thanks and Regards,
Sriram Satish
February 23, 2009 at 4:52 am
Unfortunately, your expression does not evaluate to -2875 in T-SQL. (1-(982/33))*100 is going to evaluate to -2800 because the 982/33 division is going to be integer division and drops the decimal.
To get this to evaluate correctly in T-SQL, you would need the expression to be (1-(982/33.0))*100. You either need to parse the expression and handle the integers correctly, or you need to use a CLR procedure to evaluate this expression.
February 23, 2009 at 6:52 am
Ya gotta be sneaky...
Drop table #mYhead
CREATE TABLE #MyHead
(
RowNum INT IDENTITY(1,1),
SomeDate DATETIME,
SomeType VARCHAR(10)
)
INSERT INTO #MyHead
(SomeDate, SomeType)
SELECT '20090116 01:00','AA' UNION ALL
SELECT '20090116 01:10','AA' UNION ALL
SELECT '20090116 01:20','AA' UNION ALL
SELECT '20090116 01:30','AA' UNION ALL
SELECT '20090116 01:00','BB' UNION ALL
SELECT '20090116 01:10','BB' UNION ALL
SELECT '20090116 01:20','BB' UNION ALL
SELECT '20090116 01:30','BB' UNION ALL
SELECT '20090116 01:40','BB' UNION ALL
SELECT '20090116 01:50','BB' UNION ALL
SELECT '20090116 01:40','AA' UNION ALL
SELECT '20090116 01:50','AA' UNION ALL
SELECT '20090116 02:00','BB' UNION ALL
SELECT '20090116 02:10','BB'
SELECT *,1 AS SomeSequence FROM #MyHead order by rownum
PRINT DATALENGTH(REPLACE('abcdef', 'cde', 'xxx'))
declare @t table(ID int,Expression nvarchar(100))
insert into @t
select 1,'8*(1/2)-6' union all
select 2,'278*(1/4)-2' union all
select 3,'81*(3/5) +4'
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'select '+cast(id as varchar(10))+','
+REPLACE(REPLACE(REPLACE(Expression,'*','.0*'), '/','.0*'),' ','')
+' union all ' from @t
set @sql=left(@sql,len(@sql)-9)
print @SQL
exec(@sql)
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2009 at 7:03 am
Jeff - I think you have an error in your outer REPLACE - it should be a / rather than a *.
You are going to have trouble if someone actually types in an expression with correct data types: '(1-(982.0/33.0))*100' or if someone uses some extra parenthesis: '(1-((982)/(33)))*100'.
February 23, 2009 at 8:24 am
Michael Earl (2/23/2009)
Jeff - I think you have an error in your outer REPLACE - it should be a / rather than a *.You are going to have trouble if someone actually types in an expression with correct data types: '(1-(982.0/33.0))*100' or if someone uses some extra parenthesis: '(1-((982)/(33)))*100'.
Yep... it's easier to use a calulator or a spreadsheet for this. 😉 Most folks that want to do this type of thing end up having identical calculations with different values. It would be easier to hard code the calculation and put the values in a lookup table.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2009 at 9:31 am
dynamic sql is always the answer to these but are you sure you want to do that on the server ?
* Noel
February 23, 2009 at 12:28 pm
[font="Verdana"]Can't use dynamic SQL inside a UDF.[/font]
February 23, 2009 at 12:54 pm
As Bruce pointed out, you cannot use Dynamic SQL inside a SQL UDF. If you can bypass the need for an actual function, then Dynamic SQL will do the job for you (using Jeff's trick to fix the Integer divide problem).
However, if you absolutely have to have this in a UDF, then this is one of the very few cases when a CLR UDF is the best answer (IMHO), which should have no trouble parsing and evaluating an arithmetic expression. (Of course, you could also do that with T-SQL, but you're definitely using the wrong tool for the job there).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 12:57 pm
RBarryYoung (2/23/2009)
If you absolutely have to have this in a UDF, then this is one of the very few cases when a CLR UDF is the best answer (IMHO).
[font="Verdana"]Agreed. Some security implications to opening up CLR on SQL Server preclude being able to do that on some production systems. But if you can swing it, the CLR is a far better approach for this kind of thing.[/font]
February 23, 2009 at 1:19 pm
What security implications would that be? So far as I know, it's just disabled by default as a Surface Area issue: That is, don't enable things that you aren't using or aren't managing. Which pretty much applies to all of the optional features.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 1:24 pm
[font="Verdana"]Um... y'know, I'm not sure what the "security implications" are (if any). Caught me out. Just quoting the DBA who refused to allow us to enable CLR.[/font]
February 23, 2009 at 1:30 pm
Yeah, they tend to say that because Microsoft says it. But Microsoft never explains why they say it, except as part of the general "Surface Area" security policy. I think that that's all it really is.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 1:35 pm
RBarryYoung (2/23/2009)
Yeah, they tend to say that because Microsoft says it. But Microsoft never explains why they say it, except as part of the general "Surface Area" security policy. I think that that's all it really is.
...which was a wholesale departure from "let's leave everything turned on by default", to more of a "only turn things on you specifically need" in the 2005 install routine. Meaning - a baby step in making the thing secure out of the box. Not speicific to CLR at all, but as you pointed out - to various optional pieces.
They didn't quite get there IMO, but it's a start.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2009 at 1:51 pm
Agreed. I don't disagree with the Surface Area principle (Use less and Disable what you aren't using) to minimize exposure, it's one of the core principals of modern computer security management. But people (DBA's, managers, auditors, etc.) tend to forget the "...if you aren't using it." part.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply