float to varchar with no trailing zeros

Question

I am having a heck of a time trying to convert a 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

Answer 1

SQL Server 2012 and up support FORMAT, which enables .NET-like format strings, making this a rather easy task; just use

FORMAT(Num, '0.#####')

Working SQL Fiddle here.

Answer 2

Ok, so here is my solution. It takes advantage of the optional arguments for STR and is definitely not pretty, but it works. Really, string manipulation (which this is) should be done at a higher level.

DECLARE @Test TABLE (FloatValue FLOAT(53) NOT NULL);

INSERT INTO @Test (FloatValue)
VALUES (10)
       , (.302563)
       , (423546532)
       , (34532.46524556)
       , (654.048);

WITH CTE_Base
AS
    (   SELECT FloatValue
               , StrValue = REPLACE(LTRIM(RTRIM(STR(FloatValue, 50, 5))), '.00000', '')
               , DecPosition = CHARINDEX('.', REPLACE(LTRIM(RTRIM(STR(FloatValue, 50, 5))), '.00000', ''))
               , Reversed = REVERSE(REPLACE(LTRIM(RTRIM(STR(FloatValue, 50, 5))), '.00000', ''))
               , LastNonZeroPos = PATINDEX(
                                      '%[^0]%', REVERSE(REPLACE(LTRIM(RTRIM(STR(FloatValue, 50, 5))), '.00000', '')))
        FROM @Test)
SELECT *
       , FinalValue = SUBSTRING(StrValue
                                       , 1
                                       , CASE WHEN DecPosition = 0
                                                   THEN LEN(StrValue)
                                             ELSE          LEN(StrValue) - LastNonZeroPos + 1
                                              END)
FROM CTE_Base;

Answer 3

This is pretty ugly, but should do what you need (could always turn it into a function).

Basically we convert to a numeric type (gets rid of the scientific notation elements and limits the number after the decimal point), then we do a bunch of replacements for 0's and for the . so that you don't end up with the trailing zeroes or a funky looking number.

DECLARE @table1 TABLE
(
    [Num] FLOAT(53)
);

INSERT INTO @table1
VALUES
(10 ),
(.302563),
(423546532),
(34532.46524556);

SELECT Num,
       CAST(Num AS VARCHAR) AS One,
       STR(Num) AS Two,
       STR(Num, 10, 5) AS Three,
       REPLACE(
                  RTRIM(REPLACE(
                                   REPLACE(
                                              RTRIM(REPLACE(CONVERT(VARCHAR(20), CONVERT(NUMERIC(20, 5), Num)), 0, ' ')),
                                              ' ',
                                              '0'
                                          ),
                                   '.',
                                   ' '
                               )
                       ),
                  ' ',
                  '.'
              ) AS Four
FROM @table1;