convert character to number
Posted on 2014-10-06
We have oracle 11gr2.
I have a free hand field where the users can key in amount values. But in the application it is defined as varchar. I need to convert this into string. The values can be some time a space, or number with commas. It can potentially have $ signs also.
I want a conversion where if the value is blank spaces then 0, if there are commas or $ sign or any other symbols other than period sign, it should be stripped and provide number as output, if there is a period, it should be preserved.
blank space(s) 0
4 668 5 46685
please let me know if this can be done using regular expressions or any other way