7 Replies Latest reply on Oct 21, 2010 1:45 PM by 789895

# How to convert string containing decimal point into integer in pl sql

How to convert string containing decimal point into integer
in Pl sql ?

eg : string 12.5 into integer 12.5
• ###### 1. Re: How to convert string containing decimal point into integer in pl sql
Use TO_NUMBER function.
• ###### 2. Re: How to convert string containing decimal point into integer in pl sql
Search for TO_NUMBER.
• ###### 3. Re: How to convert string containing decimal point into integer in pl sql
To_number wont work for decimal string like 12.5
• ###### 4. Re: How to convert string containing decimal point into integer in pl sql
user9117604 wrote:
To_number wont work for decimal string like 12.5
Please demonstrate how it won't work.

Here's how it does work ...
``````SQL> select to_number('12.5') from dual;

TO_NUMBER('12.5')
-----------------
12.5

SQL> select to_number('   12.5   ') from dual;

TO_NUMBER('12.5')
-----------------
12.5``````
• ###### 5. Re: How to convert string containing decimal point into integer in pl sql
Hi,
user9117604 wrote:
To_number wont work for decimal string like 12.5
Post some code that people can run to re-create the problem, and explain what that code should be doing.
user9117604 wrote:
How to convert string containing decimal point into integer
in Pl sql ?

eg : string 12.5 into integer 12.5
Actually, 12.5 is not an integer. 12 is an integer, so is 13, but no number between 12 and 13 (exclusive) is an integer.

TO_NUMBER won't necessarily return an integer. You need to use ROUND (or TRUNC, or FLOOR, or CEIL) on the results to make sure you end up with an integer.
``x := ROUND (TO_NUMBER (str));``
if you assign a non-integer value to an integer variable or column, the value will automatically be ROUNDed.
• ###### 6. Re: How to convert string containing decimal point into integer in pl sql
EdStevens wrote:
>

Please demonstrate how it won't work.
No problem. OP is most likely lives in the part of the world where fractional part separator is comma not dot:
``````SQL> alter session set nls_numeric_characters = '.,'
2  /

Session altered.

SQL> select to_number('12.5') from dual
2  /

TO_NUMBER('12.5')
-----------------
12.5

SQL> alter session set nls_numeric_characters = ',.'
2  /

Session altered.

SQL> select to_number('12.5') from dual
2  /
select to_number('12.5') from dual
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select to_number('12.5','fm999999.99999999','nls_numeric_characters = ''.,''') from dual
2  /

TO_NUMBER('12.5','FM999999.99999999','NLS_NUMERIC_CHARACTERS=''.,''')
---------------------------------------------------------------------
12,5

SQL>``````
SY.
• ###### 7. Re: How to convert string containing decimal point into integer in pl sql
Hi,

As posted in the earlier topic of yours

You can use the following functions to convert String to a number