CODEWITHSUNDEEP
sqlsql-serverfloating-point
Miles
Miles

Reputation: 5726

SQL Convert float to comma varchar with decimals

I have a couple floats that are kinda big. They're around a 100 million.

I would like this number to show like the following 123,456,789.01234

I've found that I can use CONVERT if its a money datatype but this doesn't do the full trick (it leaves off some decimal places).

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

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

Thanks

*** Update

Upvotes: 3

Views: 19505

Answers (2)

SWD
SWD

Reputation: 289

if you are just displaying this 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>

the MySQL function rounds

Upvotes: 0

Learning
Learning

Reputation: 8185

DECLARE @f FLOAT

SET @f = 123456789.01234

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

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

Upvotes: 5

Related Questions