Want To Convert From Character Format To Number Format With Decimal

want to convert character format (00001000000) as 10000.00. Please help me. I've already tried with select to_number('00012300','9999999999.99','nls_numeric_characters = ''.,''')

Solution 1:

Well, you can convert your value to a number using

selectto_number('00012300') from dual;

It seems that you divide by 100.

So

selectto_number('00012300') / 100 from dual;

If you want to display two decimals, back to a varchar again, with a format

selectto_char(to_number('00012300') / 100, '999999D99')  from dual;

Solution 2:

What you simply may use, is a workaround like that:

CAST(round(0000000111100 / 100, 2) AS numeric(38, 2))

So what you do is that you round your value by 2 decimal places while dividing by 100 and then casting it to to a numeric with a 2 piece left-over.

If your value is a string, just simply convert it to an integer before.

Peace