Mask for field number

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
claudiofreis01
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 14 Jan 2014 10:05 pm

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
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 248
Joined: Fri, 06 Feb 2009 3:02 pm
Location: ERECHIM - RS

Try this format:

Select all

Lpad(to_char(vn_valor,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.'''),15,' ')
claudiofreis01
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 14 Jan 2014 10:05 pm

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
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 248
Joined: Fri, 06 Feb 2009 3:02 pm
Location: ERECHIM - RS

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 14 Jan 2014 10:05 pm

I performed this select up but did not return anything :?
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest