MySQL format number with unknown number of decimal places-CodePudding

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.

CodePudding user response：

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