Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Convert float into varchar in SQL Server without scientific notation and trimming decimals.

For example:

I have the float value 1000.2324422 , and then it would be converted into varchar as same 1000.2324422 .

There could be any number of decimal values...the float value comes randomly.

Casting or converting to VARCHAR(MAX) or anything else did not work for me using large integers (in float fields) such as 167382981, which always came out '1.67383e+008'.

What did work was STR() .

Neither str() or cast(float as nvarchar(18)) worked for me.

What did end up working was converting to an int and then converting to an nvarchar like so:

 convert(nvarchar(18),convert(bigint,float))
The STR function works nice. I had a float coming back after doing some calculations and needed to change to VARCHAR, but I was getting scientific notation randomly as well. I made this transformation after all the calculations:
 ltrim(rtrim(str(someField)))
                This helps a lot. Also, be aware that the default length for str is 10. So it will get truncated if you assign to a fixed length varchar less than this. For example:      declare @x varchar(9) = str(1); will assign 9 spaces to @x
– andyb
                Jun 13, 2013 at 15:21
                yes, i m using the same solution, but i also specify the precision like in this post: stackoverflow.com/questions/3715675/…
– elle0087
                Aug 1, 2016 at 8:16
                For me, str works for integer values, but it rounds non-integer ones to the nearest int.  So str(0.2) returns 0.  This on MSSQL 2008 R2.
– Ed Avis
                Apr 26, 2019 at 13:40
Casting as DECIMAL will put decimal point on every value, whether it
had one before or not.
Casting as VARCHAR allows you to use the REPLACE function
First REPLACE zeros with spaces, then RTRIM to get rid of all trailing spaces (formerly zeros), then REPLACE remaining spaces with zeros.
Then do the same for the period to get rid of it for numbers with no decimal values.
                Bravo!  This is the only solution I've seen that handles non-integer values correctly, and doesn't cruft up the results with leading or trailing zeroes or spaces.  I created a user-defined function to do it.  Should I edit the answer to give it as a function definition?
– Ed Avis
                Apr 26, 2019 at 13:47
This is not relevant to this particular case because of the decimals, but may help people who google the heading.
Integer fields convert fine to varchars, but floats change to scientific notation. A very quick way to change a float quickly if you do not have decimals is therefore to change the field first to an integer and then change it to a varchar.
I have another solution since the STR() function would result some blank spaces, so I use the FORMAT() function as folowing example:
SELECT ':' + STR(1000.2324422), ':' + FORMAT(1000.2324422,'##.#######'), ':' + FORMAT(1000.2324422,'##')
The result of above code would be:
:      1000 :1000.2324422   :1000
Below is an example where we can convert float value without any scientific notation.
DECLARE @Floater AS FLOAT = 100000003.141592653
SELECT CAST(ROUND(@Floater, 0) AS VARCHAR(30))
      ,CONVERT(VARCHAR(100), ROUND(@Floater, 0))
      ,STR(@Floater)
      ,LEFT(FORMAT(@Floater, ''), CHARINDEX('.', FORMAT(@Floater, '')) - 1)
SET @Floater = @Floater * 10
SELECT CAST(ROUND(@Floater, 0) AS VARCHAR(30))
      ,CONVERT(VARCHAR(100), ROUND(@Floater, 0))
      ,STR(@Floater)
      ,LEFT(FORMAT(@Floater, ''), CHARINDEX('.', FORMAT(@Floater, '')) - 1)
SET @Floater = @Floater * 100
SELECT CAST(ROUND(@Floater, 0) AS VARCHAR(30))
      ,CONVERT(VARCHAR(100), ROUND(@Floater, 0))
      ,STR(@Floater)
      ,LEFT(FORMAT(@Floater, ''), CHARINDEX('.', FORMAT(@Floater, '')) - 1)
SELECT LEFT(FORMAT(@Floater, ''), CHARINDEX('.', FORMAT(@Floater, '')) - 1)
      ,FORMAT(@Floater, '')
In the above example, we can see that the format function is useful for us. FORMAT() function returns always nvarchar.
Your field = Float field for convert
Length = Total length of your float number with Decimal point
Scale = Number of length after decimal point
For example:
SELECT STR(1234.5678912,8,3)
The result is: 1234.568
Note that the last digit is also round up.
You will have to test your data VERY well. This can get messy. Here is an example of results simply by multiplying the value by 10. Run this to see what happens.
On my SQL Server 2017 box, at the 3rd query I get a bunch of *********. If you CAST as BIGINT it should work every time. But if you don't and don't test enough data you could run into problems later on, so don't get sucked into thinking it will work on all of your data unless you test the maximum expected value.
 Declare @Floater AS FLOAT =100000003.141592653
    SELECT CAST(ROUND(@Floater,0) AS VARCHAR(30) ), 
            CONVERT(VARCHAR(100),ROUND(@Floater,0)), 
            STR(@Floater)
    SET  @Floater =@Floater *10
    SELECT CAST(ROUND(@Floater,0) AS VARCHAR(30) ), 
            CONVERT(VARCHAR(100),ROUND(@Floater,0)), 
            STR(@Floater)
    SET  @Floater =@Floater *100
    SELECT CAST(ROUND(@Floater,0) AS VARCHAR(30) ), 
            CONVERT(VARCHAR(100),ROUND(@Floater,0)), 
            STR(@Floater)
                Can you be more specific than "This can get messy" and "run into problems"? What can happen exactly? What is the result of the example? Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).
– Peter Mortensen
                Jul 27, 2021 at 15:57
There are quite a few answers but none of them was complete enough to accommodate the scenario of converting FLOAT into NVARCHAR, so here we are.
This is what we ended up with:
DECLARE @f1 FLOAT = 4000000
DECLARE @f2 FLOAT = 4000000.43
SELECT TRIM('.' FROM TRIM(' 0' FROM STR(@f1, 30, 2))),
       TRIM('.' FROM TRIM(' 0' FROM STR(@f2, 30, 2)))
SELECT CAST(@f1 AS NVARCHAR),
       CAST(@f2 AS NVARCHAR)
Output:
------------------------------ ------------------------------
4000000                        4000000.43
(1 row affected)
------------------------------ ------------------------------
4e+006                         4e+006
(1 row affected)
In our scenario the FLOAT was a dollar amount to 2 decimal point was sufficient, but you can easily increase it to your needs.
In addition, we needed to trim ".00" for round numbers.
None of the previous answers for me. In the end I simply used this:
INSERT INTO [Destination_Table_Name]([Field_Name])
SELECT CONCAT('#',CAST([Field_Name] AS decimal(38,0))) [Field_Name]
FROM [dbo].[Source_Table_Name] WHERE ISNUMERIC([CIRCUIT_NUMBER]) = 1
INSERT INTO [Destination_Table_Name]([Field_Name])
SELECT [Field_Name]
FROM [dbo].[Source_Table_Name] WHERE ISNUMERIC([CIRCUIT_NUMBER]) <> 1
                Thank you for contributing an answer. Would you kindly edit your answer to to include an explanation of your code? That will help future readers better understand what is going on, and especially those members of the community who are new to the language and struggling to understand the concepts.
– Jeremy Caney
                Dec 11, 2020 at 21:35
                Also, as part of that, it may be worth pointing out why the other answers didn't work. Was there something specifically wrong with them such as a syntax or logic error? That's especially useful to contrast with the top-voted answer.
– Jeremy Caney
                Dec 11, 2020 at 21:36
select format(convert(float,@your_column),'0.0#########')
Advantage: This solution is independent of the source datatype (float, scientific, varchar, date, etc.)
String is limited to 10 digits, and bigInt gets rid of decimal values.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.