Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227

    Unanswered: thousands separator

    Hi,
    I would like to display large numbers with separators for the thousands, for example 1234567 would become 1,234,567. Can I arrange this with some kind of session setting or do I need to create a function to arrange this.

    Thanks
    Johan

  2. #2
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    If in SQL*Plus, try this:

    col test form 999,999
    select 123456 test from dual;

    will result in:

    TEST
    --------
    123,456
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  3. #3
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    is it possible to put this in query, like this:
    select format(some_field, 999,999.00) from dual
    This is because I'm using Delpi as programming tool on an Oracle database.
    Johan

  4. #4
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107
    Originally posted by jora
    is it possible to put this in query, like this:
    select format(some_field, 999,999.00) from dual
    This is because I'm using Delpi as programming tool on an Oracle database.

    Hi,
    Try this.
    SELECT TO_CHAR(sal, '999,999.00') FROM emp
    nn

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697

    Re: thousands separator

    Hi Jora,

    For purely running scripts through SqlPlus, NoviceNo1 is quite right and you should not see any problems, but to a client side Application such as Delphi it's a different matter.

    You can change session settings (google groups on borland.public.delphi for "alter session set nls" for the why's and why nots) but it is not recommended to format numbers before they arrive Delphi side.

    If you do a to_char() on your number columns you *will* have later problems when a user on a different client locale runs it. You will have the same problems with dates if you force them to_char. You will also have to convert them back to int/float/date etc client side to work on them.

    If you want to pass dates and number as part of a query, only ever use bind variables, these will work regardless of client locale and session nls parameters.

    If you wish to display queried columns client side in a particular format, use the .TIntegerField.DisplayFormat, TDateField.DisplayFormat properties of your DataSet.

    There is one exception to this, if you're using the dbExpress drivers to connect, dbExpress has a known bug where it only support american numeric characters (,.).

    Do check out google, but if you've still got probs post again with more specific details of the problem and I'll try to help.

    HTH
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •