convert float into varchar in SQL server without scientific notation

Question

convert float into varchar in SQL server without scientific notation and trimming decimals   for Ex   i have float value 1000 2324422  then it would be converted into varchar as same 1000 2324422   there would have any number of decimal values  float value comes randomly

User · Answer

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.

User · Answer

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    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

User · Answer

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

User · Answer

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 point  2-for two digits after decimal point

User · Answer

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

User · Answer

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

User · Answer

STR function works nice  I had float coming back after doing some calculations and needed to change to VARCHAR  but was getting scientific notation randonly as well  I made this transformation after all the calcs   ltrim rtrim str someField

User · Answer

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  quot  00 quot  for round numbers

User · Answer

This works   Suppose  dbo AsDesignedBites XN1E1   4016519 564    For the following string    POLYGON    STR dbo AsDesignedBites XN1E1  11  3

User · Answer

None of the above worked 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    lt  gt  1

User · Answer

select format convert float  your column   0 0             Advantage  This solution is irrespective of the source datatype  float  scientific  varchar  date etc  String is limited to 10 digits  bigInt gets rid of decimal values

User · Answer

This works CONVERT VARCHAR 100   CONVERT DECIMAL 30  15   fieldname

User · Answer

Try CAST CAST  value AS bigint  AS varchar

User · Answer

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

User · Answer

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

User · Answer

You can use this code  STR  lt Your Field gt   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   Result is  1234 568 Note that the last digit is also round up  Good luck

[sql-server] convert float into varchar in SQL server without scientific notation

Examples related to sql-server