Post reply

How do I display thousand separator for floats?

  • April 21, 2009 at 9:16 am

    #129596

    I am working with an application that lets me create filter lists of data for display in a dashboard using SQL statements.

    The filter and dashboard components of the application don't appear to use the Regional Settings for displaying Values, so if I have a long number like 7265342.12 it looks really bad in the filter/dashboard and is difficult to read.

    I would like it to read 7,265,342.12 so I think I will need to convert it to a string in my SQL statements but am not sure how to do this, and a search on this forum for "thousand separator" returns no results.

    Any ideas?

  • April 21, 2009 at 9:55 am

    #981579

    Here you go. Cast/Convert the float data to dataype money

    then the outer CONVERT will insert the commas for you.

    declare @test-2 float

    set @test-2 = 7265342.12

    select @test-2, convert(varchar(20),cast(@test as money),1)

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • April 21, 2009 at 10:02 am

    #981585

    Thanks Bob - that's great!

    That's a lot neater than a string conversion.

    Many thanks.

  • April 21, 2009 at 10:07 am

    #981590

    You're very welcome, Dizzy.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • April 21, 2009 at 1:49 pm

    #981759

    Bob Hovious (4/21/2009)

    Here you go. Cast/Convert the float data to dataype money

    then the outer CONVERT will insert the commas for you.

    declare @test-2 float

    set @test-2 = 7265342.12

    select @test-2, convert(varchar(20),cast(@test as money),1)

    Hi Bob

    Very nice solution! Never thought about this way... - still my mentor 🙂

    Greets

    Flo

  • April 21, 2009 at 2:34 pm

    #981803

    Flo:

    After following the Tally thread, there is no way I can claim to be your mentor. However you are welcome to any little tips or techniques I can share.

    Best regards,

    Bob

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • April 21, 2009 at 2:37 pm

    #981808

    Bob Hovious (4/21/2009)

    Flo:

    After following the Tally thread, there is no way I can claim to be your mentor. However you are welcome to any little tips or techniques I can share.

    Best regards,

    Bob

    After the Tally thread I'm just "The Splitter"...

  • April 21, 2009 at 2:48 pm

    #981816

    I'm about to test my only thought on the matter. If it seems to perform well, I'll send it to you.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • April 21, 2009 at 3:01 pm

    #981825

    Bob Hovious (4/21/2009)

    I'm about to test my only thought on the matter. If it seems to perform well, I'll send it to you.

    Sure, I would be happy about! And I'm already curious 😉

    Greets

    Flo

  • April 22, 2009 at 12:37 pm

    #982423

    Dizzy (4/21/2009)

    I am working with an application that lets me create filter lists of data for display in a dashboard using SQL statements.

    The filter and dashboard components of the application don't appear to use the Regional Settings for displaying Values, so if I have a long number like 7265342.12 it looks really bad in the filter/dashboard and is difficult to read.

    I would like it to read 7,265,342.12 so I think I will need to convert it to a string in my SQL statements but am not sure how to do this, and a search on this forum for "thousand separator" returns no results.

    Any ideas?

    Bob's solution is definitely the easiest from a database standpoint but I have to ask, doesn't whatever you have to build the dashboard application have any built in formatting for display fields?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 23, 2009 at 1:32 am

    #982682

    No formatting options whatsoever I'm afraid. All data is also left justified automatically which is not very nice for numerics. Nothing I can do about that.

    I do have some numbers that are quantities rather than values that I would prefer to show to 1dp rather than the 2dp that MONEY gives. I want to retain the thousand separator and decimal point then but only show 1 dp for these values.

    Am I able to do that too somehow?

  • April 23, 2009 at 6:50 am

    #982820

    Dizzy (4/23/2009)

    I do have some numbers that are quantities rather than values that I would prefer to show to 1dp rather than the 2dp that MONEY gives. I want to retain the thousand separator and decimal point then but only show 1 dp for these values.

    Am I able to do that too somehow?

    Building on Bob's previous example:

    declare @test-2 float, @test2 varchar(20)

    set @test-2 = 7265342.12

    set @test2 = convert(varchar(20),cast(@test as money),1)

    select @test-2, left(@test2, CHARINDEX('.', @test2)+1)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 23, 2009 at 6:54 am

    #982826

    You don't really have the kind of control you do in Excel. However, after the convert, you already have it in string format. All you have to do is take the left of the string, less one character. It reads a little cumbersome, but it works.

    declare @x float

    set @x = -72254.334

    select left(convert(varchar(10),cast(@x as money),1),len(convert(varchar(10),cast(@x as money),1))-1)

    -- CTEs can help readability

    ;with cte1 (X) as (select -72254.334)

    ,cte2 (X) as (select convert(varchar(10),cast(X as money),1) from cte1)

    ,cte3 (X) as (select left(X,len(X)-1) from cte2)

    select X from cte3

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • April 23, 2009 at 8:22 am

    #982947

    Both are good solutions, but I prefer Wayne's method just because it's a bit easier on the eyes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 23, 2009 at 8:36 am

    #982960

    Thanks guys. I used Wayne's solution and it worked fine.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply