convert character to number

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.

eg:

blank space(s)       0
1,123,456               1123456
$1,456                    1456
$4256                      4256
4 668 5                    46685

please let me know if this can be done using regular expressions or any other way

Thanks
gs79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
nvl(regexp_replace(yourfield,'[^0-9.]'),0)

and then if you want the resulting string to be a number type, wrap the whole thing in to_number


to_number(nvl(regexp_replace(yourfield,'[^0-9.]'),0))
0
HuaMin ChenProblem resolverCommented:
Try
select to_number(replace('1,123,456',',','')) from dual;
select to_number(replace(replace('$1,456',',',''),'$','')) from dual;
select to_number(replace('4 668 5 ',' ','')) from dual;

Open in new window

0
HuaMin ChenProblem resolverCommented:
And this
select to_number(replace('$4256','$','')) from dual;

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

awking00Commented:
Hopefully, there are no europeans entering values like 2.367,83 :-)
0
sdstuberCommented:
based on previous examples that seems either unlikely, or prohibited or out-of-scope.

I recommend not using the simple REPLACE method since, as shown in the examples, it can only handle one character at a time.

You could, if you knew all possible illegal characters nest a bunch of them in series, but regexp is much simpler and doesn't require an explicit blacklist, only the accepted whitelist of characters
0
gs79Author Commented:
sdstubder, I think the function works for most of the cases. Can it be modified to handle negative. ie -125 should print as -125. However if dash(-) is in the middle of the number it should be elliminated 1-25 should print as 125

Thanks
0
awking00Commented:
There may be a more elegant way, but the following should work -
As an example -
SQL> select * from charnums;
YOURFIELD
-----------------------------
123.45-23
-123.45-23

123.45
-123.45
 -123.45-23
$123.45-23
-123,45-23

select yourfield characters,
to_number(nvl(decode(substr(ltrim(yourfield),1,1),'-','-'||regexp_replace(yourfield,'[^0-9.]'),regexp_replace(yourfield,'[^0-9.]')),0)) numbers
from charnums;

CHARACTERS                        NUMBERS
------------------------------ ----------
123.45-23                        123.4523
-123.45-23                      -123.4523
                                        0
123.45                             123.45
-123.45                           -123.45
 -123.45-23                     -123.4523
$123.45-23                       123.4523
-123,45-23                       -1234523
0
sdstuberCommented:
something like this?  A two-phase cleanse.
First get rid of anything that isn't a digit, a period or a minus-sign.
Second pass, remove minus-signs that aren't the first character

REGEXP_REPLACE(
           NVL(REGEXP_REPLACE(yourfield, '[^-0-9.]'), 0),
           '[^0-9.]',
           NULL,
           2
       )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
sdstuber,
Knew you'd find a more elegant way. If I had thought of using the second position to start the second replace, I might have simply removed the dash (as opposed to not removing numeric characters or periods) :-)
REGEXP_REPLACE(
           NVL(REGEXP_REPLACE(yourfield, '[^-0-9.]'), 0),
           '-',
           NULL,
           2
       )
0
awking00Commented:
gs79,
Please do NOT give me any points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.