MySQL format number with unknown number of decimal places

Question

In MySQL, I only want to add thousand separator in the number like 1234.23234, 242343.345345464, 232423.22 and format to "1,234.23234", "242,343.345345464", "232,423.22", use format function need to specify the number of decimals, is there any other function can format the number with unknown number of decimal places? for 1234.23234, I do not want to get the result like 1234.2323400000 or 1234.23, just want to get 1,234.23234.

Answer 1

As suggested split the string drop the trailing zeros format the number before the decimal point and concat taking into account the possibility of no decimals being present at all for example

set @a = 1234.56;

select 
case when instr(@a,'.') > 0 then
  concat(
  format(substring_index(@a,'.',1),'###,###,###'),
  '.',
  trim(trailing '0' from substring_index(@a,'.',-1))
  ) 
else
format (@a,'###,###,###')
end formatted

Answer 2

MySQL doesn't seem to have such feature. You'll probably need to write a custom function based on FORMAT() plus some string manipulation to remove trailing zeroes after the comma, for example using REGEXP_REPLACE(). The default locale used in FORMAT() is en_US, which seems to be the one you want, so you can omit it or provide your own should you need a different locale.

WITH sample_data (sample_number) AS (
    SELECT NULL
    UNION ALL SELECT 0
    UNION ALL SELECT 0.00001
    UNION ALL SELECT 100.01
    UNION ALL SELECT 100.0102
    UNION ALL SELECT 100.012300456
    UNION ALL SELECT 1000
    UNION ALL SELECT 123456789.87654321
    UNION ALL SELECT -56500.333
)
SELECT
    sample_number,
    REGEXP_REPLACE(
        FORMAT(sample_number, 999),
        '(\.\\d*[1-9])(0+$)|(\.0+$)',
        '$1'
    ) AS USA,
    -- Replace \. with , for locales that use comma as decimal separator:
    REGEXP_REPLACE(
        FORMAT(sample_number, 999, 'de_DE'),
        '(,\\d*[1-9])(0+$)|(,0+$)',
        '$1'
    ) AS Germany
FROM sample_data;

sample_number	USA	Germany
NULL	NULL	NULL
0.000000000	0	0
0.000010000	0.00001	0,00001
100.010000000	100.01	100,01
100.010200000	100.0102	100,0102
100.012300456	100.012300456	100,012300456
1000.000000000	1,000	1.000
123456789.876543210	123,456,789.87654321	123.456.789,87654321
-56500.333000000	-56,500.333	-56.500,333

Fiddle