Oracle: Convert string to double

Question

This is my query for a database pull:

SELECT DISTINCT
TEMPLATE_GROUP_PROPERTIES.PROPERTYTYPE,
PROPERTY.PROPERTYVAL

FROM
TEMPLATE_GROUP_PROPERTIES

LEFT OUTER JOIN PROPERTY_DATA
ON (TEMPLATE_GROUP_PROPERTIES.PROPERTYGROUPID = PROPERTYDATA.PROPERTYGROUPID)

WHERE
PROPERTY.PROPERTYVAL = :propValue

Whoever created the database made the Property.Propertyval column a column defined as a string when it is represented as scientific notation numbers (4.0E-3, 2.0E2, etc). I need to convert either the string to a double or the double to a string whatever way would work and I don't know how. I have tried messing around with the TO_NUMBER() function and have found nothing of help so far.

Answer 1

If you really know that the string is a valid number, then use cast():

WHERE cast(property.propertyval as float) = :propValue

Some cautions.

First, the use of the function will prevent the query from using an index on propertyval. You can always create a functional index if you need an index.

Second, comparisons of floating point values can be problematic when the values are very close. You might consider:

WHERE abs(cast(property.propertyval as float) - :propValue) < 0.001

Or some other threshold value.