Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: formating numbers, how do i format:

    a number like 1,234,456.82 ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Totally agreed....
    unless like me you have some admin scripts that run in SSMS and would like to get formatted numbers without the hassle of building an uneccessary GUI.
    Code:
    CONVERT(VARCHAR, CAST(mycolumn AS MONEY), 1)

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by mikezx10
    a number like 1,234,456.82 ?
    -- =============================================
    CREATE FUNCTION [dbo].[fn_FormatNumber]
    (
    @money varchar(255),
    @format varchar(25)
    )
    RETURNS VARCHAR(255)
    AS
    BEGIN
    DECLARE @moneyVC VARCHAR(255)
    SELECT @moneyVC =

    CASE @format
    WHEN '$#,###.##' THEN
    CASE
    WHEN cast(isnull(@money, 0) as money) < 0
    THEN '-$' + Replace(Convert(varchar(20), cast(isnull(@money, 0) as money),1), '-', '')
    ELSE Convert(varchar(20), cast(isnull(@money, 0) as money),1)
    END
    WHEN '#,###.##' THEN
    Convert(varchar(20), cast(isnull(@money, 0) as money),1)
    WHEN '#,###' THEN
    Replace(Convert(varchar(20), cast(isnull(@money, 0) as money),1), '.00', '')
    WHEN '(#,###.##)' THEN
    CASE
    WHEN cast(isnull(@money, 0) as money) < 0
    THEN '(' + Replace(Convert(varchar(20), cast(isnull(@money, 0) as money),1), '-', '') + ')'
    ELSE Convert(varchar(20), cast(isnull(@money, 0) as money),1)
    END
    WHEN '($#,###.##)' THEN
    CASE
    WHEN cast(isnull(@money, 0) as money) < 0
    THEN '($' + Replace(Convert(varchar(20), cast(isnull(@money, 0) as money),1), '-', '') + ')'
    ELSE '$' + Convert(varchar(20), cast(isnull(@money, 0) as money),1)
    END
    WHEN 'unformat' THEN
    REPLACE(REPLACE(REPLACE(@money, '(', '-'), ')', ''), ',', '')
    ELSE
    'Invalid format specified'
    END
    RETURN @moneyVC

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Excellent example of how to do something you should not do.
    Perhaps you have a recipe for cyanide souffle' you could post as well?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Why do you want to format your numbers in SQL ? Isn't it a waste of server performances ?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and converting a perfectly good numeric value to a string. Redunculous.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by blindman
    ...and converting a perfectly good numeric value to a string. Redunculous.
    Redunculous???
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  9. #9
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    wrong thread sorry
    Last edited by ortho; 12-10-07 at 13:05.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ortho
    wrong thread sorry
    Your redaculation is acceptified.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2007
    Posts
    183
    Unless OP is planning to BCP the result to a text file...

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Peso
    Unless OP is planning to BCP the result to a text file...
    i hope it's a csv!

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by jezemine
    i hope it's a csv!
    I hope its being sent to someone other than me, 'cause I don't want to have to deal with a formatted numeric string in a text file either.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pfft blindman, you just want the easy way out
    George
    Home | Blog

Posting Permissions

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