oracle thousand seperator

hi guys
I have sql
select salary from employee
100

If want a thousand seperator, how can i do it? I want the result to be
100,000
royjaydAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Easiest is to apply a format mask using to_char:

select to_char(salary,'999,999.99') from employee;

Typically in Oracle you apply 'formatting' to columns when selecting them.
0
 
DavidSenior Oracle Database AdministratorCommented:
DEF salary 9,999,999

SELECT salary....
0
 
royjaydAuthor Commented:
sorry, how can i include that  'DEF salary 9,999,999'
in my sql ?
select salary from employee

thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
>>how can i include that in my sql ?

Use to_char.
0
 
royjaydAuthor Commented:
thanks

when i do
select to_char(salary,'999,999.99') from employee;

i see ##########
For example
50,000 comes up fine but  1000000 come up as #########

i tried expanding the column width but i still see #########
Any idea ?
0
 
DavidSenior Oracle Database AdministratorCommented:
DEFINE is a SQL*Plus command, it does not require an execute delimiter (/ or ;) -- just a normal carriage return.  The next line is the SELECT statement.
0
 
slightwv (䄆 Netminder) Commented:
I should also add that you need to make sure the format mask you provide is large enough to hold the largest value returned or you'll get '#' signs.

to see this:
select to_char(12345,'9,999') from dual;
0
 
slightwv (䄆 Netminder) Commented:
>>50,000 comes up fine but  1000000 come up as #########

Just posted on this.  I guess I was typing when you posted it...
0
 
DavidSenior Oracle Database AdministratorCommented:
Slight, pardon the good-natured tease, but this method doesn't sound easiest..... :)
0
 
slightwv (䄆 Netminder) Commented:
dvz,

I don't think define will force a format mask on a selected column.  Do you have an example?
0
 
slightwv (䄆 Netminder) Commented:
>>but this method doesn't sound easiest..... :)

But it works and will work outside sqlplus?

Are you thinking about column formatting in sqlplus instead of define?
col salary format 999,999,999
0
 
DavidSenior Oracle Database AdministratorCommented:
you betcha, ty
0
 
royjaydAuthor Commented:
any help with my next question will be greatly appreciated
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.