Convert float into varchar in SQL Server without scientific notation

Try this:

SELECT REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(CAST(CAST(YOUR_FLOAT_COLUMN_NAME AS DECIMAL(18,9)) AS VARCHAR(20)),'0',' ')),' ','0'),'.',' ')),' ','.') FROM YOUR_TABLE_NAME

1. Casting as DECIMAL will put decimal point on every value, whether it had one before or not.
2. Casting as VARCHAR allows you to use the REPLACE function
3. First REPLACE zeros with spaces, then RTRIM to get rid of all trailing spaces (formerly zeros), then REPLACE remaining spaces with zeros.
4. Then do the same for the period to get rid of it for numbers with no decimal values.

This works:

CONVERT(VARCHAR(100), CONVERT(DECIMAL(30, 15), fieldname))

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.

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

You can use this code:

STR(<Your Field>, Length, Scale)

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)

Try this code

SELECT CONVERT(varchar(max), CAST(1000.2324422 AS decimal(11,2)))

Result:

1000.23

Here decimal(11,2): 11-total digits count (without the decimal point), 2 - for two digits after the decimal point

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

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.

This works:

Suppose

dbo.AsDesignedBites.XN1E1 = 4016519.564`

For the following string:

'POLYGON(('+STR(dbo.AsDesignedBites.XN1E1, 11, 3)+'...