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

Answer 1

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