Post reply

Formated (Pretty) Decimal SELECTion

  • November 26, 2003 at 3:03 pm

    #160925 

    

    /*

    I want to select a "pretty" description of a decimal numeric.

    "Pretty" = No trailing zeros

    

    I've tested a few "common" values, 

    but I'm scared I may get something like 6.8999999999

    because of FLOAT accuracy.

    

    I would rather not use a SQL function because

    it has to go against many rows, and my experience

    has been a slowdown when having to call Scalar Functions

    with a lot of rows.

    

    Ultimate output is to text files for external mobile devices.

    I have no control of the structure of the text data.

    Output Tax Descr. must be 16 char. fixed lenth left justified.

    It is one of several other columns, this script just for 

    example purposes.

    

    Any other ideas to make the output pretty, that are fast, and will never

    output .....9999999.

    

    Thanks

    */

    DECLARE @Tax1 Decimal (10,5)

    Set NoCount ON

    

    Set @Tax1 = 8

    SELECT @Tax1 as Tax1, Convert(Char(16), 'Tax 1 - ' + CONVERT(VarChar(10), @Tax1) + '%') as UglyTaxDescription,

    	Convert(Char(16), 'Tax 1 - ' + CONVERT(VarChar(10), CONVERT(FLOAT, @Tax1)) + '%') as PrettyTaxDescription 

    

    Set @Tax1 = 8.2

    SELECT @Tax1 as Tax1, Convert(Char(16), 'Tax 1 - ' + CONVERT(VarChar(10), @Tax1) + '%') as UglyTaxDescription,

    	Convert(Char(16), 'Tax 1 - ' + CONVERT(VarChar(10), CONVERT(FLOAT, @Tax1)) + '%') as PrettyTaxDescription 

    

    Set @Tax1 = 8.25

    SELECT @Tax1 as Tax1, Convert(Char(16), 'Tax 1 - ' + CONVERT(VarChar(10), @Tax1) + '%') as UglyTaxDescription,

    	Convert(Char(16), 'Tax 1 - ' + CONVERT(VarChar(10), CONVERT(FLOAT, @Tax1)) + '%') as PrettyTaxDescription



    Once you understand the BITs, all the pieces come together

  • November 26, 2003 at 5:39 pm

    #483846

    The main question is why you want to cut-off the trailing zeros when you declare the variable as DECIMAL(10,5)?



    Regards,
    kokyan

  • December 5, 2003 at 2:31 pm

    #484926

    On the Hand Held Computer display, the end user should see "8% Sales Tax" or "6.5% Sales Tax". Customer does not want to see "8.00000% Sales Tax". Some taxes may have 5 decimal places of taxation, and the mobile application supports up to 5 decimal places.



    Once you understand the BITs, all the pieces come together

  • December 5, 2003 at 3:27 pm

    #484935 

    

    CAST(LTRIM(STR(@Tax1,9,6-PATINDEX('%[^0]%',REVERSE(@Tax1)))) AS char(16))

    --Jonathan



    --Jonathan

  • December 5, 2003 at 4:01 pm

    #484940

    Thanks Jonathan,

    I'll look into deeper on Monday,

    but 1 question: Any particular reason you used Char(16) instead of VarChar(16)?



    Once you understand the BITs, all the pieces come together

Viewing 5 posts - 1 through 4 (of 4 total)

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