Formated (Pretty) Decimal SELECTion

Question

Formated (Pretty) Decimal SELECTion

SSCertifiable

Points: 7437

November 26, 2003 at 3:03 pm



/*

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

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

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

kokyan SSCommitted Points: 1694 More actions · Answer 1

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

Regards,
kokyan

ThomasH SSCertifiable Points: 7437 More actions · Answer 2

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 3



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

--Jonathan

ThomasH SSCertifiable Points: 7437 More actions · Answer 4

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