Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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