Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    67

    Unanswered: Number Formatting in SQLSERVER

    say, i have a column in the database that has number values. I want to display these number with comma separators (Eg: if the column values is 1654, then i want to display it as 1,654).

    How can i achieve this in my sQL query

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Formatting is done on the client. The simplest answer is to use a client such as MS-Access, MS-Excel, Crystal Reports, etc that does this formatting for you.

    You can choose to do the formatting on the server, but this is a really poor choice.

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's a really poor choice, but it's also a fun challenge for a monday morning

    w00h00!!
    Code:
    create table teststuff
    ( id tinyint not null primary key identity
    , foo integer
    );
    insert into teststuff(foo) values(-1)
    insert into teststuff(foo) values(0)
    insert into teststuff(foo) values(937)
    insert into teststuff(foo) values(1000)
    insert into teststuff(foo) values(345678)
    insert into teststuff(foo) values(1234567890)
    insert into teststuff(foo) values(-111111111)
    insert into teststuff(foo) values(3456789)
    insert into teststuff(foo) values(-1000)
    insert into teststuff(foo) values(-11444)
    insert into teststuff(foo) values(-1555666)
    insert into teststuff(foo) values(-1333555777)
    
    select id, foo
    , case when foo > -999 and foo < 999
           then right(space(14)
                     +cast(foo as varchar)
                     ,14)
           when foo > -999999 and foo < 999999
           then right(space(14)
                     +reverse(
                        stuff(reverse(foo),4,0,','))
                     ,14)
           when foo > -999999999 and foo < 999999999
           then right(space(14)
                     +reverse(
                       stuff(
                       stuff(reverse(foo),7,0,',')
                         ,4,0,','))
                     ,14)
           else right(space(14)
                     +reverse(
                       stuff(
                       stuff(
                       stuff(reverse(foo),10,0,',')
                         ,7,0,',')
                         ,4,0,','))
                     ,14)
         end  as formatted
    from teststuff
    
     1   -1                        -1
     2   0                          0
     3   937                      937
     4   1000                   1,000
     5   345678               345,678
     6   1234567890     1,234,567,890
     7   -111111111      -111,111,111
     8   3456789            3,456,789
     9   -1000                 -1,000
    10   -11444               -11,444
    11   -1555666          -1,555,666
    12   -1333555777   -1,333,555,777
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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