SQL Convert decimal float to varchar comma

Question

I have a couple floating, which are very large. There are about 100 million of them.

I would like this number to be displayed as follows: 123 456 789.01234

I found that I can use CONVERT if it is a data type of money, but it does not do the full trick (it leaves some decimal places).

I should have commas on the left and five decimal places on the right.

Is there a built-in SQL function to help with this? Or do I need to write a custom function?

thanks

*** Update

• I forgot to mention that I just show them as varchars. Therefore, after this there are no calculations.
• This works in a SQL database, so MySQL and Oracle will not work.

Answer 1

DECLARE @f FLOAT

SET @f = 123456789.01234

SELECT  LEFT('$' + CONVERT(VARCHAR(20), CAST(@f AS MONEY), 1), LEN(@f) - 2)

this will reduce it to two decimal places for formatting. You can change LEN (@f) - 2 to change this setting.

Answer 2

if you just display it as text, you can do the following:

oracle:

select to_char(123456789.01234,'999,999,999.99999') from dual; => 123,456,789.01234

MySQL:

select format(123456789.01234,5) => 123,456,789.01234<br>

MySQL function rounds