Oracle SQL: How to convert a number with a comma as a decimal separator to a point

Question

I have a table in an Oracle SQL database. In this table there is a numeric field NUMBER (9,2), with values that may or may not have decimals. In Spain, decimal values in Oracle SQL are shown separated by commas. For example:

• 0,22
• 123
• 15,55

How can I convert these numbers, in case they are decimal to:

• 0.22
• 123
• 15.55

I have tried to do it with the following instruction:

select TO_CHAR(FIELD_NAME, 'FM99999999D99', 'NLS_NUMERIC_CHARACTERS= ''.,''')
from table;

But the results are the following:

• .22
• 15.55

The only one who does well is the third. In the first case, the 0 in front is missing. In the second case, the point is unnecessary.

Answer 1

In this table there is a numeric field NUMBER(9,2)

A NUMBER is a binary data-type that stores an exact representation of the number with the significant figures of the value stored as 1-byte per two consecutive digits of the value. It ALWAYS uses this (proprietary) format and does NOT store decimal points or format the value in any human-readable format.

Therefore, if you have a table with a NUMBER(9,2) column which contains numeric data then you do not need to do anything; that data is already stored as numbers and the decimal places are in the correct place.

How can I convert these numbers, in case they are decimal to: 0.22, 123, 15.55

If by "convert" you mean store then you do not need to do anything as the database already stores those values and it NEVER stores a format so for 0.22 it will store the digits 22 and that the exponent is -2 (shifting it 2-digits to the right of the decimal point). Similarly 123 will be stored as 1 in one byte and 23 in another byte and an exponent of 0 and 15.55 will be stored as the bytes 15 and 55 with an exponent of -2.

If by "convert" you mean display then you can use:

SELECT field_name,
       TRIM(
         TRAILING '.' FROM
         TO_CHAR(
           field_name,
           'FM99999990D99',
           'NLS_NUMERIC_CHARACTERS= ''.,'''
         )
       ) AS formatted_value
FROM   table_name;

Which, for the sample data:

ALTER SESSION SET NLS_TERRITORY = 'Spain';

CREATE TABLE table_name (field_name NUMBER(9,2));
INSERT INTO table_name (field_name)
SELECT   0.22 FROM DUAL UNION ALL
SELECT 123    FROM DUAL UNION ALL
SELECT  15.55 FROM DUAL;

Outputs:

FIELD_NAME	FORMATTED_VALUE
,22	0.22
123	123
15,55	15.55

fiddle