Having the number: 103,648.340 format it to be as the expected result

Expect result: 103 648.34

My progress:

SELECT
    TRIM(price) 0 as price_without_decimal_zeros,
    FORMAT(price, 1, 'bg_BG') as thousand_delimited_by_space
from products

1. TRIM remove decimal zeros, so I've 103,648.34
2. FORMAT add thousand delimited by space, so I've 103 648.340

I've tried to combine TRIM with FORMAT without success

CodePudding user response：

Try this solution:

SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from REPLACE(format(price, 3), ",", " ") )) AS price
FROM products
WHERE price LIKE '%.%'

DEMO