June 21st, 2011, 09:00 AM
Evaluate numeric expression in varchar field
I've a varchar field that may contain expressions like this one:
when I place a select query like this:
SELECT discount,cast(discount as float) as .... from .....
it gives error on cast(discount as float) e.g. cannot evaluate 10+5
how can I evaluate directly ?
In SQL server I can write:
select cast(substring(discount,1,Patindex('%+%', discount)-1) as float) + cast(substring(discount,Patindex('%+%', discount)+1,len(discount)) as float) from ...
but here, and without using UDF?
June 21st, 2011, 04:11 PM
Are you sure you want to do this on the server?
Given the fact that this is a varchar field, the complexity of the transformation
and the number of possible error conditions that could stop the SELECT in its tracks,
this looks like a job better suited to a full featured programming language as it
iterates through a dataset returned to the client.