replace some symbols and convert value into number
marcoMay 17 2011 — edited May 17 2011Hi all. I've got table bp_link with "avalue" column VARCHAR2 (200). So I can find different version of percent here:
a) 100%
b) 100
c) 100,00
d) 100.00
e) null
f) space symbol
(I hope all versions are mentioned).
So I'm trying to replace some symbols and convert value into number using steps below:
nvl(avalue,0) - removes null
replace(nvl(avalue,0),'%','') - removes '%' symbol
replace(replace(nvl(avalue,0),'%',''),',','.') - removes ',' symbol
trim(replace(replace(nvl(avalue,0),'%',''),',','.')) - removes space symbol
to_char(trim(replace(replace(nvl(avalue,0),'%',''),',','.')), 'FM999999999999.90') - converts into "100.00" format.
But it still doesn't work, maybe I can't see something else.
When I simply "select * from ..." it works:
select to_char(trim(replace(replace(nvl(avalue,0),'%',''),',','.')), 'FM999999999999.90')
as avalue from bp_link dd where roletypeid = 9
But when I try "order by avalue" it ends with error - ORA-01722: invalid number
select to_char(trim(replace(replace(nvl(avalue,0),'%',''),',','.')), 'FM999999999999.90')
as avalue from bp_link dd where roletypeid = 9
order by dd.avalue
What else can I do?