MySQL TRUNCATE or ROUND with variable decimal leng

问题:

I am not able to TRUNCATE (or round) a decimal on variable decimal lengh. Here's my MySQL query:

SELECT
TRUNCATE( `amount` , `decimal_length` ) AS truncated_decimal,
FROM table

-- sample data
amount    decimal length
123.123    0
456.456    1
789.789    2

--expected outcome
truncated_decimal
123
456.4
789.78

--current outcome (wrong)
truncated_decimal
123.123
456.456
789.789

My truncated_decimal variable is returned WITHOUT truncation, i.e. default decimal length from table. Same behavior with ROUND function. Variable decimal_length is an integer that is different for different rows in table.

Any ideas?

回答1:

So the correct answer to get variable length decimals is to use FORMAT function:

SELECT
FORMAT( `amount` , `decimal_length` ) AS truncated_decimal,
FROM table

It does go with rounding, but at least you get needed variable length decimals.

回答2:

Use Trim to remove trailing zero's

SELECT 
TRIM(TRAILING '0' FROM TRUNCATE( `amount` , `decimal_length` ))
FROM table

回答3:

-- Example with a field name
SELECT
ROUND( `field_name` , 2 ) AS truncated_decimal
FROM table

-- Example with just a number
SELECT
ROUND( '285,1234' , 2 ) AS truncated_decimal

==> 285,12

You mix up truncate function with round function

Is this what you are looking for?