Solved

oracle thousand seperator

Posted on 2013-02-04
14
340 Views
Last Modified: 2013-02-04
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
Comment
Question by:royjayd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
14 Comments
 
LVL 23

Assisted Solution

by:David
David earned 50 total points
ID: 38851749
DEF salary 9,999,999

SELECT salary....
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 38851755
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
 

Author Comment

by:royjayd
ID: 38851757
sorry, how can i include that  'DEF salary 9,999,999'
in my sql ?
select salary from employee

thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

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

Use to_char.
0
 

Author Comment

by:royjayd
ID: 38851785
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
 
LVL 23

Expert Comment

by:David
ID: 38851789
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38851790
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38851794
>>50,000 comes up fine but  1000000 come up as #########

Just posted on this.  I guess I was typing when you posted it...
0
 
LVL 23

Expert Comment

by:David
ID: 38851800
Slight, pardon the good-natured tease, but this method doesn't sound easiest..... :)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38851803
dvz,

I don't think define will force a format mask on a selected column.  Do you have an example?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38851825
>>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
 
LVL 23

Expert Comment

by:David
ID: 38851863
you betcha, ty
0
 

Author Closing Comment

by:royjayd
ID: 38851952
any help with my next question will be greatly appreciated
thanks
0
 

Author Comment

by:royjayd
ID: 38851953
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question