Convert string to number with decimals

user545194

Is it possible to convert this string to a number with decimals? The source is coming from an Excel file as a string like the example below.

For example:

String: 0.00389

Desired conversion as a number: 3.89

BluShadow

You mean like..

SQL> select to_number('0.00389')*1000 from dual;


TO_NUMBER('0.00389')*1000
-------------------------
                     3.89

Frank Kulash

Hi, @user545194

String: 0.00389

Desired conversion as a number: 3.89

To convert the string to a number (or, more precisely, to generate a number from the string), use the TO_NUMBER function. It looks like you want to do something more, like change the units (from kilograms to grams, e.g.). To do that, you can multiply the number returned by TO_NUMBER. for example:

SELECT   str
,	 TO_NUMBER (str) * 1000 AS num
FROM	 table_x
;

TO_NUMBER with just one argument accepts any valid format. Some rows can have '987.000', others can have '3.14' and still others can have '.123'

User_H3J7U

https://community.oracle.com/tech/developers/discussion/comment/16807540#Comment_16807540

nls dependency.

BluShadow

https://community.oracle.com/tech/developers/discussion/comment/16807545#Comment_16807545

Well, rather than be a critic, why not show the necessary adaption needed for that?

User_H3J7U

https://community.oracle.com/tech/developers/discussion/comment/16807552#Comment_16807552

select 3.89 num from sourcetable where str='String: 0.00389';

user545194

Thanks to all for your valuable feedback!

To be more specific, the string is supposed to resemble a fractional number and not a decimal number according to our business unit.

@Frank Kulash You are right, this number should by shown as a unit, ie. 3,78 percent. So the desired output should be something like this:

Value in field: 0.00378

Output: 3,78

But I must clarify with the business unit how the original value should be interpreted, because the value could be 0.017, 0.0017, 0.00017

Frank Kulash

Hi, @user545194

To be more specific, the string is supposed to resemble a fractional number and not a decimal number according to our business unit.

Please post al your requirements up front. You'll get better answers faster if you say exactly what you need to do, rather than saying something vague, waiting for people to give answers to something that is not your question, and then adding beiong a tiny bit more specific. Post a little sample data (5 or 10 rows, CRETATE TABLE and INSERT statements) and the exact results you want from that data. Explain why you want those results from that data. Not everyone who wants to help you is as familiar with your application as you are, so explain any application-specific terms you use. For example, what is your "business unit"?