#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    5
    Rep Power
    0

    Evaluate numeric expression in varchar field


    I've a varchar field that may contain expressions like this one:
    10+5

    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?


    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    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.

    Clive

IMN logo majestic logo threadwatch logo seochat tools logo