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.

Accepted Answer

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.

Oracle: Convert string to double

Solution

Problem