patternsqlMinor
float to varchar with no trailing zeros
Viewed 0 times
zerosfloatwithvarchartrailing
Problem
I am having a heck of a time trying to convert a
The field could have a number that is really big or really small. I need to convert it to VARCHAR and show, at most, 5 digits after the decimal but no trailing zeros. So 123.456789 should be 123.45678 and 123456.78 should be 123456.78.
I have an SQL Fiddle showing what I have tried: http://sqlfiddle.com/#!6/bd392/4
FLOAT(53) field to a VARCHAR where there are no trailing zeros or scientific notation.The field could have a number that is really big or really small. I need to convert it to VARCHAR and show, at most, 5 digits after the decimal but no trailing zeros. So 123.456789 should be 123.45678 and 123456.78 should be 123456.78.
I have an SQL Fiddle showing what I have tried: http://sqlfiddle.com/#!6/bd392/4
Solution
SQL Server 2012 and up support
Working SQL Fiddle here.
FORMAT, which enables .NET-like format strings, making this a rather easy task; just useFORMAT(Num, '0.#####')Working SQL Fiddle here.
Code Snippets
FORMAT(Num, '0.#####')Context
StackExchange Database Administrators Q#193433, answer score: 6
Revisions (0)
No revisions yet.