Mask for field number

[claudiofreis01]

Hello everyone,
I am trying to insert data from a spreadsheet in the bank with fields in a format that contains thousand, hundred and ten example: 1,234.43
The problem is that I have hundreds 234.43 and when I can fix a field It's the problem in another.
When the field does not have the dozen it mixes example: what would be 5.45 it is 5.45
and a thousand that would be 2,765 this 27.65
I already tried several forms but this example I mentioned this way:

Select all

SELECT replace(replace(replace(TRIM(TO_CHAR(unit_price,'999,990.00','nls_numeric_characters='',.''')),',','*'),'.',','),'*','.') FROM

The field is type number and I am using a package.
If anyone can help thank you because I have tried all the formats I found on the net.
Thanks.

[marlonpasquali]

Try this format:

Select all

Lpad(to_char(vn_valor,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.'''),15,' ')

[claudiofreis01]

With these formatts it gets lost when the hundred has ten example when it has only 1234,23 it gets correct 1,234,25, when the value is hundred gets correct example: 340 is 340.00 but it err when it has the ten example the value 365 , 15 is 36,515.00 understand?

[marlonpasquali]

Hello,
I did this select below and worked out.

Select all

select Lpad(to_char(365.15,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.'''),15,' ') from dual

The difference I have seen in relation to thy is the formatting I used: 9G999G990D00

[claudiofreis01]

I performed this select up but did not return anything