Solved

convert character to number

Posted on 2014-10-06
10
228 Views
Last Modified: 2014-10-22
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
Comment
Question by:gs79
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40365238
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
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40365296
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
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40365590
And this
select to_number(replace('$4256','$','')) from dual;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Expert Comment

by:awking00
ID: 40366571
Hopefully, there are no europeans entering values like 2.367,83 :-)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40366582
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
 

Author Comment

by:gs79
ID: 40367359
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
 
LVL 32

Expert Comment

by:awking00
ID: 40368269
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40368297
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
 
LVL 32

Expert Comment

by:awking00
ID: 40368371
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
 
LVL 32

Expert Comment

by:awking00
ID: 40368377
gs79,
Please do NOT give me any points.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query to identify changes between rows of two tables 8 55
1 FROM DUAL wont work with additional columns ?? 4 46
Use of Exception to end a Loop 3 45
Repeat query 13 61
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question