• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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
0
gs79
Asked:
gs79
  • 4
  • 3
  • 2
  • +1
1 Solution
 
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
 
HuaMinChenSystem managerCommented:
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
 
HuaMinChenSystem managerCommented:
And this
select to_number(replace('$4256','$','')) from dual;

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now