CODEWITHSUNDEEP
sqloracle-databasenumber-formatting
NoDisplayName
NoDisplayName

Reputation: 15736

How to select numbers with precision in Oracle

I have a query and in some of columns, it returns numbers, like 1,2,3 and etc but I need to show them as 1,00, 2,00, 3,00 respectfully, how can I achieve it? I tried 

CAST(2 as FLOAT(2))

and

ROUND(2, 2)

but those didn't work. I also tried to use to_char, but I didnt understand how to use masking.

Upvotes: 2

Views: 5565

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Set your nls_numeric_characters according to your requirement for the separator. Use TO_CHAR to format the output.

SQL> alter session set nls_numeric_characters=',.';

Session altered.

SQL> WITH DATA(num) AS(
  2  SELECT 1 FROM dual UNION ALL
  3  SELECT 2 FROM dual UNION ALL
  4  SELECT 3 FROM dual
  5  )
  6  SELECT to_char(num, '9D99') FROM DATA;

TO_CH
-----
 1,00
 2,00
 3,00

SQL>

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231651

Assuming that your goal is to select a character string that represents the number and that you always want 2 decimal places to be displayed and for the string to respect your session's NLS settings for the decimal separator (whether that is a comma or a period)

select to_char( <<the number>>, '0D99' )
  from <<your table>>

This will print a leading 0 as well if the number is less than 1. If you don't want a leading 0, use the "9D99" format mask

Upvotes: 4

Related Questions