Hi,
Anyone know if it’s possible to default the display of numeric data to be comma-ified ? I.e. display the number 1234567 as 1,234,567 ?
That’s no good as it would change the datatype to char. I want to change the display properties, not the data itself. Plus it’s not a default setting as far as I can tell.
Hi Wim,
Use the TO_CHAR() function? TO_CHAR(123456789,
'999,999,999') does the trick.
That's fine but will always put a comma in as the grouping separator. If
the client as set the NLS_NUMERIC_CHARACTERS to something different then
the returned format will be incorrect. You need to use 'G' for the comma
and 'D' for the decimal:
-- UK default:
ALTER SESSION set NLS_NUMERIC_CHARACTERS=".,";
SELECT to_char(1234567890.616,'999G999G999G999D999') FROM dual;
1,234,567,890.616
SELECT to_char(1234567890.616,'999,999,999,999.999') FROM dual;
1,234,567,890.616
-- European default:
ALTER SESSION set NLS_NUMERIC_CHARACTERS=",.";
SELECT to_char(1234567890.616,'999G999G999G999D999') FROM dual;
1.234.567.890,616
SELECT to_char(1234567890.616,'999,999,999,999.999') FROM dual;
1,234,567,890.616
-- Something stupid:
ALTER SESSION set NLS_NUMERIC_CHARACTERS="sd";
SELECT to_char(1234567890.616,'999G999G999G999D999') FROM dual;
1s234s567s890d616
SELECT to_char(1234567890.616,'999,999,999,999.999') FROM dual;
1,234,567,890.616
Unfortunately, while Toad gives us the opportunity to specify a date
format and a time format plus, we get to define the
NLS_NUMERIC_CHARACTERS within the options, these characters are not used
unless we use TO_CHAR() and a format string that included 'G' and 'D'. I
think that Bob was afetr the default for all number type columns to be
returned using the defined characters - like dates and times do
automagically.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Hi Bob,
That's no good as it would change the datatype to char. I
want to change the display properties, not the data itself.
Plus it's not a default setting as far as I can tell.
you can set the comma and decimal characters in Toad's options
(View->options->general) but unless you use a TO_CHAR with a 'D' and an
'G' (Decimal and Group separator) they don't get used for numbers.
Unlike dates and times. 
I also think that you need to restart Toad after changing these as well.
They don't appear to have any effect in the current or new sessions.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Hi Wim,
Thanks, I was too lazy to inspect the options of TO_CHAR().
No worries.
I'm not sure if I want automatic delimiters added to my
numbers. Especially if the numbers are ID's, then that looks
very strange to me.
I'm sure that there would be an option!
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
I think what the poster wants is to change how data is shown in grids. We
already have this for dates. In the Toad Options (DATA GRID) we can specify how
dates are shown, how the time portion is shown, how nulls are shown. Why not
also be able to show how numbers are shown.
I agree with Wim that this would be of limited use.
I can imagine that you want money to be displayed like 999g999g999d99,
but things like ID's you just want as plain numbers. (At least, I do).
Peter
Erwin Rollauer schreef:
I think what the poster wants is to change how data is shown in grids.
We already have this for dates. In the Toad Options (DATA GRID) we can
specify how dates are shown, how the time portion is shown, how nulls
are shown. Why not also be able to show how numbers are shown.From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] *On Behalf Of
*Wim de Lange
Sent: December 17, 2009 6:14 AM
To: toad
Subject: Re: [toad] Re: Comma-ify NumbersI don't understand the problem then.
The data is not changed. Nothing changes in the database. You only want
another visual representation? Then to_char() is the function for that.Or if you want to show the data in another way, you can export the data
to Excel or create a report (in Toad) on it, where you have options to
format numeric data.Groetjes,
WimOn Thu, Dec 17, 2009 at 09:08, bob_flem1ng
wrote:
That's no good as it would change the datatype to char. I want to change
the display properties, not the data itself. Plus it's not a default
setting as far as I can tell.
That’s exactly what I’m after.
I get confused when I see data in a grid, and I can’t tell if the format
of the data is due to my NLS parameters, or b/c Toad is behind the scenes
manipulating the format.
I’d rather control the format of the data explicitly with my
queries…esp if I were to ever run said queries outside of Toad.
There is just one problem. As someone mentioned. There are often numeric Ids. Do you want them to be shown with commas (or periods depending on option) also. Because if you don’t want them shown with commas then there is an option nightmare.
I have to agree. Although there are times where monetary values are difficult to
read in the grid and I have to resort to selecting with a to_char wrapper to
make them more readable, I’m not sure there’s any way for the GUI to
determine when to format and not format. Almost every normalized structure will
contain some surrogate key that shouldn’t be formatted even though
it’s a number. Probably the same with most ID type information.
Maybe if it could be made smart enough to not format numbers with a decimal
precision of 0? I’m thinking that the chances are if the number has
decimals it’s usually either monetary or would benefit from formatting.
I’m sure someone will come up with an exception to that rule though, since
as we know, the only rule that has no exception is that there is an exception to
just about every rule.
There is just one problem. As someone mentioned. There are often numeric Ids.
So… I seem to see a challenge: how many different number types can you
name?
There is just one problem. As someone mentioned. There are often
numeric Ids.So... I seem to see a challenge: how many different number types can you
name?
Even more than that, I'm afraid.
One of my instructors from 20 years ago used to wax poetic about the Good
Ol' Days working on coding sheets outside in the courtyards at Bell Labs
back in the 60s. One rule of hers I've remembered and use it to this day
when I don't know if I should make a column character or numeric: Unless
you're doing math on it, make it a character.
There are always exceptions, but many a maintenance nightmare has been
thankfully avoided by that simple foresight. Unless you've never been asked
to put a "J" into an otherwise incrementing order "number".
When dealing with displaying numbers in columns, my only hard rule is to
keep the unit of measure consistent. The trend is to make numeric columns
in "human-readable" format (using a suffix of "K" for 1000s, "M" for
1000000s, etc). Makes it much much much more difficult for visual pattern
matching to find anomalies and trends in the data.
Woah. I should be getting back to work now...
Rich -- [Team$.02]
Disclaimer: One light goes out, the all go out!
Unless you're doing math on it, make it a character
An excellent rule to follow. One that I firmly follow myself. That does decrease
the number of different formats that should be applied. However, that
doesn’t change the reality that a numeric calculable column can still be
formatted for different purposes and therefore should have per-column
formatting. Dollars vs percentages for example.
Roger S.
I realise that many people may not be interested in achieving what I’m after, and I agree it would likely be implemented as an option. But please, I’m more interested in whether it’s possible than whether it would interest anyone else. It may not be what you want, but please just post a response a reply if it’s relevant to the question.
Thanks.
Sorry, yes I am referring to data grids.
I don’t see numeric ids as represent a problem - they’re just numbers. I think applying a format mask to them would be just as valid to improving readability.