Solved

convert character to number

Posted on 2014-10-06
10
224 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 73

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

Expert Comment

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

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

 

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 73

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now