Oracle: Convert string to double
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.
Related videos on Youtube
bbesase
Updated on August 25, 2022Comments
-
bbesase over 1 year
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 theTO_NUMBER()
function and have found nothing of help so far.