how to fix oracle ORA-01722 invalid number error

Question

I have Oracle db with these inputs on column1(NUMBER(22,6)) in myTable.

0
199935,15
1026299

I want to display these columns like that:

00000000000000000.000000
00000000000199935.150000
00000000001026299.000000

My query:

SELECT trim(to_char(trim(replace(column1,',','.')),'9999999999999990.999999')) 
FROM myTable;

But Oracle shows this error. How can I fix?

01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

What is the type of your column? Is it a number or a string? — Aleksej, Mar 11 '19 at 17:57

score 3 · Accepted Answer · answered Mar 11 '19 at 18:07

3

If you have a numeric column, all you have to do is use a to_char with the right parameters; this should do the work:

select to_char(column1, '00000000000000000D000000', 'NLS_NUMERIC_CHARACTERS = ''.,''') from ...

answered Mar 11 '19 at 18:07

Aleksej

1 Answers1