Solved

oracle thousand seperator

Posted on 2013-02-04
14
338 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
  • 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
Industry Leaders: 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

Industry Leaders: 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

Suggested Solutions

Title # Comments Views Activity
Oracle encryption 12 59
oracle differnce between two timestamps 5 38
join a table with user_tab_columns in oracle 3 66
PL/SQl Expanding the WHERE statement in query 3 33
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

685 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