A coworker and I were looking at a request where we needed to write float values to a file, but have the values be of a uniform length (In this case, the float column was storing a monetary value, which isn’t a good idea, but that’s what we had to work with). In this case, all values were out to 4 decimal places, so it was a matter of padding the value to the left of the decimal to get everything to an equal length. The first thought was to convert the value to a string, pad the from with zeros, and from that take a string of the specified length.
To my surprise, it turns out that casting a float to a string data type (a varchar in this case) ends up rounding the value in some cases, and converting to scientific notation in others. With a little Googling we found out that using the STR function is the recommended way to make this conversion. STR takes has length and decimal value parameters to control how many digits are output.
I had used the FORMAT function for dates before, but it also handles numbers as well. This turned out to be the approach that was used, since in the FORMAT function we can specify padding to a certain length.
Here’s SQL for some test data long with the different approaches.
create table dbo.FloatTest( FloatValue float not null ); insert into dbo.FloatTest values (123.45678); insert into dbo.FloatTest values (2.66993256); insert into dbo.FloatTest values (0.00001); insert into dbo.FloatTest values (55555.84); insert into dbo.FloatTest values (321.0987654); select FloatValue as OriginalValue, cast(FloatValue as varchar(15)) as CastValue, str(FloatValue, 20, 10) as StrValue, format(FloatValue, 'F9') as FormatValue, format(FloatValue, '0000000000.##########') as CustomFormatValue from dbo.FloatTest; go
With FORMAT, we can specify a standard format for floats with F, followed by the number of decimal digits. Or we can create a custom format. Using a 0 for a required digit and a # for an optional digit allows us greater control over what is returned.