Solved

convert character to number

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

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

786 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