Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

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
0
royjayd
Asked:
royjayd
  • 6
  • 4
  • 4
2 Solutions
 
DavidSenior Oracle Database AdministratorCommented:
DEF salary 9,999,999

SELECT salary....
0
 
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
 
royjaydAuthor Commented:
sorry, how can i include that  'DEF salary 9,999,999'
in my sql ?
select salary from employee

thanks
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now