Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

replace some symbols and convert value into number

marcoMay 17 2011 — edited May 17 2011
Hi 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?
This post has been answered by Nimish Garg on May 17 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 14 2011
Added on May 17 2011
8 comments
1,409 views