Convert String to Decimal in pl sql

Question

I want to convert a string to a number with one decimal place. Ex:

4 after conversion -> 4.0
4.1 after conversion -> 4.1
04.1 after conversion -> 4.1
04.0 after conversion -> 4.0

I have tried

SELECT TO_NUMBER('04.0','99.99') FROM dual;

But it will not convert the string to decimal number. instead it convert to a integer (4).

score 0 · Answer 1 · answered Feb 13 '19 at 16:29

0

you may try the TO_CHAR

SELECT TO_CHAR('04.0','99.9') FROM dual;

answered Feb 13 '19 at 16:29

Brayan Espinoza

56
1
5

Thank you for your answer. Yes it should be SELECT TO_CHAR('0.1', '0D9')FROM dual; Otherwise it will result 0.9 as .9 after the conversion – Thilina De Silva Feb 13 '19 at 16:40

Kaushik Nayak · Accepted Answer · 2019-02-13T16:51:26.083

0

But it will not convert the string to decimal number. instead it convert to a integer (4).

Not really. The format of the number displayed is purely client dependant ( SQL * plus / SQL Developer etc ) and has nothing to do with how Oracle internally stores your numbers, which depends on the precision you specified for that column.

In order to see numbers in your desired format regardless of the client and assuming your territory's decimal separator is '.'( NLS_NUMERIC_CHARACTERS is '.,'), you may simply use TO_CHAR

SELECT TO_CHAR('04.0','0D9') FROM dual;
4.0

edited Feb 13 '19 at 16:51

answered Feb 13 '19 at 16:32

Kaushik Nayak

30,772
5
32
45

Thank you for your answer. Yes it should be SELECT TO_CHAR('0.1', '0D9')FROM dual; Otherwise it will result 0.9 as .9 after the conversion – Thilina De Silva Feb 13 '19 at 16:42
@ThilinaDeSilva : You're welcome and thanks for confirming. I've edited my answer accordingly. – Kaushik Nayak Feb 13 '19 at 16:52

score 0 · Answer 3 · answered Feb 15 '19 at 14:24

0

Declare the number varible like this

number(4,1);

Example:

declare
  pizza   number(4,1);
begin
  pizza := 4.1; 
  DBMS_OUTPUT.put_line(TO_CHAR(pizza,'0D9'));
end;

answered Feb 15 '19 at 14:24

Daniel Stahl

1

Convert String to Decimal in pl sql

3 Answers3