Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table your_table (fnm) as
select '4,37' from dual
union all
select '5,48' from dual;
2 rows affected

alter session set nls_numeric_characters = '.,';
-- expected to fail as D expects .
select fnm, to_number(fnm, '99D99')
from your_table
order by to_number(fnm, '99D99');
ORA-01722: invalid number

-- wrong result as comma is group separator
select fnm, to_number(fnm, '99,99')
from your_table
order by to_number(fnm, '99,99');


FNM
TO_NUMBER(FNM,'99,99')


4,37
437

5,48
548


select fnm, to_number(fnm, '99D99', 'NLS_NUMERIC_CHARACTERS=,.')
from your_table
order by to_number(fnm, '99D99', 'NLS_NUMERIC_CHARACTERS=,.');


FNM
TO_NUMBER(FNM,'99D99','NLS_NUMERIC_CHARACTERS=,.')


4,37
4.37

5,48
5.48


update your_table set fnm = '5.48' where fnm = '5,48';
1 rows affected

select fnm, to_number(replace(fnm, ',', '.'), '99.99')
from your_table
order by to_number(replace(fnm, ',', '.'), '99.99');


FNM
TO_NUMBER(REPLACE(FNM,',','.'),'99.99')


4,37
4.37

5.48
5.48


select fnm, to_number(replace(fnm, ',', '.'), '99D99', 'NLS_NUMERIC_CHARACTERS=.,')
from your_table
order by to_number(replace(fnm, ',', '.'), '99D99', 'NLS_NUMERIC_CHARACTERS=.,');


FNM
TO_NUMBER(REPLACE(FNM,',','.'),'99D99','NLS_NUMERIC_CHARACTERS=.,')


4,37
4.37

5.48
5.48


alter session set nls_numeric_characters = ',.';
update your_table set fnm = '5,48' where fnm = '5.48';
1 rows affected

-- expected to work as D now expects ,
select fnm, to_number(fnm, '99D99')
from your_table
order by to_number(fnm, '99D99');


FNM
TO_NUMBER(FNM,'99D99')


4,37
4,37

5,48
5,48


-- fails as comma is group seperator
select fnm, to_number(fnm, '99,99')
from your_table
order by to_number(fnm, '99,99');
ORA-01722: invalid number

select fnm, to_number(fnm, '99D99', 'NLS_NUMERIC_CHARACTERS=,.')
from your_table
order by to_number(fnm, '99D99', 'NLS_NUMERIC_CHARACTERS=,.');


FNM
TO_NUMBER(FNM,'99D99','NLS_NUMERIC_CHARACTERS=,.')


4,37
4,37

5,48
5,48


update your_table set fnm = '5.48' where fnm = '5,48';
1 rows affected

select fnm, to_number(replace(fnm, ',', '.'), '99.99')
from your_table
order by to_number(replace(fnm, ',', '.'), '99.99');


FNM
TO_NUMBER(REPLACE(FNM,',','.'),'99.99')


4,37
4,37

5.48
5,48


select fnm, to_number(replace(fnm, ',', '.'), '99D99', 'NLS_NUMERIC_CHARACTERS=.,')
from your_table
order by to_number(replace(fnm, ',', '.'), '99D99', 'NLS_NUMERIC_CHARACTERS=.,');


FNM
TO_NUMBER(REPLACE(FNM,',','.'),'99D99','NLS_NUMERIC_CHARACTERS=.,')


4,37
4,37

5.48
5,48