How to convert Float to Varchar in SQL

Question

I am doing this

declare @num float = 7708369000

select  @num as [float], 
    convert(varchar, @num) as [varchar]

it gives me this

float                  varchar
---------------------- ------------------------------
7708369000             7.70837e+009

But I want this

float                  varchar
---------------------- ------------------------------
7708369000             7708369000

Please help.

Simple google search "Formatting a float in varchar but NOT in scientific notation" **Result**: http://bytes.com/topic/sql-server/answers/80603-formatting-float-varchar-but-not-scientific-notation — dani herrera, Jul 20 '13 at 09:58

score 26 · Accepted Answer · answered Jul 20 '13 at 09:57

26

convert it to decimal first,

CAST(CAST(@num AS DECIMAL(20)) AS VARCHAR(20))

SQLFiddle Demo

answered Jul 20 '13 at 09:57

John Woo

258,903
69
498
492

3

what about fractional part? – EvAlex Jun 09 '14 at 07:37
this does not appear to be correct as it truncates the fractional part, thus only works correct for numbers which have 0 in the fractional portion – CodeCowboyOrg May 13 '15 at 17:13
4

Change DECIMAL(20) to DECIMAL(20, S) where S is the desired number of decimal places. S (scale) must be less than or equal to P (precision) for datatype DECIMAL(P, S). – Rieekan Jul 28 '16 at 19:45

Siddhant Swami · Answer 2 · 2013-07-20T10:20:58.360

8

Try using the Str function rather than using convert

DECLARE @num float = 7708369000;    
SELECT Str(@num);

edited Jul 20 '13 at 10:20

answered Jul 20 '13 at 10:02

Siddhant Swami

323
1
3
14

Sorry for the earlier one, wrote 25 and 0 parameters by mistake. The above edited solution should work fine and should not introduce any white spaces. – Siddhant Swami Jul 20 '13 at 10:22
this does not appear to be correct as it truncates the fractional part, thus only works correct for numbers which have 0 in the fractional portion – CodeCowboyOrg May 13 '15 at 17:24

score 2 · Answer 3 · answered May 22 '15 at 14:46

Try one of the following variations (values represent Max Value of BigInt and other Max type values) that meet your needs below. http://sqlfiddle.com/#!6/745c8f/1

CREATE TABLE Table1 
(
   FloatDefault float,  --Default is 24 bits giving 7 digit precision and 4 bytes storage
   Float24 float(24),   --Max for 4 bytes storage, 24 bits giving 7 digit precision and 4 bytes storage
   Float53 float(53)    --Max for 8 bytes storage, 53 bits giving 15 digit precision and 8 bytes storage
);

INSERT INTO Table1 VALUES(-9223372036854775808, -9223372036854775808, -9223372036854775808); --Max Negative Value of a Big Int
INSERT INTO Table1 VALUES(9223372036854775807, 9223372036854775807, 9223372036854775807);  --Max Positive Value of a Big Int
INSERT INTO Table1 VALUES(-2147483648, -2147483648, -2147483648); --Max Negative Value of a Big Int
INSERT INTO Table1 VALUES(2147483647, 2147483647, 2147483647);  --Max Positive Value of a Big Int
INSERT INTO Table1 VALUES(123456789012345678901234567890, 123456789012345678901234567890, 123456789012345678901234567890);
INSERT INTO Table1 VALUES(123456789012345678901234567890.12345678, 123456789012345678901234567890.12345678, 123456789012345678901234567890.12345678);
INSERT INTO Table1 VALUES(1234567890, 1234567890, 1234567890);
INSERT INTO Table1 VALUES(1234567890.0123456789, 1234567890.0123456789, 1234567890.0123456789);
INSERT INTO Table1 VALUES(22.0/7.0, 22.0/7.0, 22.0/7.0); -- Value of Pi
INSERT INTO Table1 VALUES(1, 1, 1);
INSERT INTO Table1 VALUES(2.0, 2.0, 2.0);
INSERT INTO Table1 VALUES(2000.0, 2000.0, 2000.0);

SELECT 
   FloatDefault,
   Float24,
   Float53,   
   CAST(CAST(Float53  AS NUMERIC(38)) AS VARCHAR(100)), -- 38 is the max precision 
   CAST(CAST(Float53  AS NUMERIC(38,5)) AS VARCHAR(100)), 
   STR(Float53),
   STR(Float53, 38),
   STR(Float53, 38,5),
   LTRIM(RTRIM(STR(Float53, 38,5))),
   CONVERT(VARCHAR, Float53),
   CONVERT(VARCHAR(100), Float53),
   CONVERT(NUMERIC(38,5), Float53)
FROM Table1
GO

score 2 · Answer 4 · answered Jun 25 '18 at 15:05

Use following function:

STR(number, length, decimal)

number is the numeric value to convert to a string
length is the length of the result string. Default value is 10
decimal is the number of decimals places to round off. Default value is 0

Ref: STR (Transact-SQL)

How to convert Float to Varchar in SQL

4 Answers4

Linked