SQL Server Performance

formatting money values

Discussion in 'General Developer Questions' started by ykchakri, Sep 24, 2003.

  1. ykchakri New Member

    I have a column with money data type, having varying length values. How can I make these values to be fixed in length? For example a value of 3.30 should be displayed as 000003.30
  2. gaurav_bindlish New Member

    I think you will have to manually write in some code in your select statement or application code to do that. Else you'll have to save it as character string and format it before storing.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. ykchakri New Member

    Can you give me a sample statement to save it as a character string using my example above ?
  4. gaurav_bindlish New Member

    DECLARE @AMOUNT MONEY -- INPUT DATA
    ,@LENGTH INT -- DESIRED FIXED LENGTH
    SELECT @AMOUNT = 3.30
    ,@LENGTH = 9
    SELECT REPLICATE('0', @LENGTH - LEN(CAST(@AMOUNT AS VARCHAR(10)))) + CAST(@AMOUNT AS VARCHAR(10))

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. bambola New Member

    or

    SELECT RIGHT('000000' + CAST(@AMOUNT as varchar), 9)

    Bambola.
  6. ykchakri New Member

    Thanks Bambola and Gaurav,

    This appears to be much easier than I thought.

Share This Page