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.
This question is related to
sql-server
This works CONVERT(VARCHAR(100), CONVERT(DECIMAL(30, 15), fieldname))
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)
Result is: 1234.568
Note that the last digit is also round up.
Good luck.
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)))
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
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]) <> 1
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
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.
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))
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
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)
This works:
Suppose
dbo.AsDesignedBites.XN1E1 = 4016519.564`
For the following string:
'POLYGON(('+STR(dbo.AsDesignedBites.XN1E1, 11, 3)+'...
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.
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()
.
Try CAST(CAST(@value AS bigint) AS 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
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.
Source: Stackoverflow.com