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.
2 Answers
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 
1 Comment
ysth
format's second parameter is a number of digits; supplying '###,###,###' gives a "Truncated incorrect INTEGER value" warning and is treated like 0. But 0 is exactly what you want
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 |
FORMAT()plus some string manipulation to remove trailing zeroes after the comma.Alter table yourtablename modify column yourcolumnName double(11,2)the 11 inside the parenthesis is the max count of whole number while the 2 is the count of decimal places.TRIM(TRAILING '0' FROM {formatted value})and remove excess zeros.