float value converting to exponential while inserting to varchar field

Question

Post reply

float value converting to exponential while inserting to varchar field

ravi@sql

SSCrazy

Points: 2121
More actions
August 6, 2015 at 12:55 am

#319559

HI Champs,
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006
but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue
It would be great help if any body give soultion for my problem
thanks
Ravi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Arvind.patil SSC Veteran Points: 214 More actions · Answer 1

Instead of writing convert(float,column_name) use convert (numeric(X,Y),column_name) .this would solve your problem

Or

You can first cast it to Sql_variant and then to Varchar

Example

convert(varchar,Convert(Sql_variant,column_name))

ravi@sql SSCrazy Points: 2121 More actions · Answer 2

ravi@sql

SSCrazy

Points: 2121

August 6, 2015 at 1:25 am

#1817769

Tons of thanks

it works very well 🙂

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 3

ravi@sql (8/6/2015)
HI Champs,
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006
but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue
It would be great help if any body give soultion for my problem
thanks
Ravi

Any conversion from float to string will either limit the length of the output to 6/8 digits and/or use scientific notation. To get the same results as displayed when doing a select, first convert to a decimal and then to a string.

😎

String to Float to String

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo..#TEST_FLOAT_CONVERT') IS NOT NULL DROP TABLE #TEST_FLOAT_CONVERT;

GO

CREATE TABLE #TEST_FLOAT_CONVERT

(

FLOAT_STR VARCHAR(50) NOT NULL

)

INSERT INTO #TEST_FLOAT_CONVERT(FLOAT_STR)

VALUES

('23382.1526382838')

,('31993.3828627108')

,('43482.2643844591')

,('42386.3374095946')

,('13371.3925976317')

,('39725.5073221224')

,('33049.8977154242')

,('21167.5444017486')

,('36290.2935231998')

,('17492.2977907421');

SELECT

TFC.FLOAT_STR AS FLOAT_STR

,CONVERT(FLOAT,TFC.FLOAT_STR,0) AS FLOAT_VALUE

,CONVERT(VARCHAR(40),CONVERT(DECIMAL(38,15),TFC.FLOAT_STR,0),0) AS FLOAT_DECIMAL_CHAR

FROM #TEST_FLOAT_CONVERT TFC;

Output

FLOAT_STR FLOAT_VALUE FLOAT_DECIMAL_CHAR

------------------ ---------------------- ----------------------

23382.1526382838 23382.1526382838 23382.152638283800000

31993.3828627108 31993.3828627108 31993.382862710800000

43482.2643844591 43482.2643844591 43482.264384459100000

42386.3374095946 42386.3374095946 42386.337409594600000

13371.3925976317 13371.3925976317 13371.392597631700000

39725.5073221224 39725.5073221224 39725.507322122400000

33049.8977154242 33049.8977154242 33049.897715424200000

21167.5444017486 21167.5444017486 21167.544401748600000

36290.2935231998 36290.2935231998 36290.293523199800000

17492.2977907421 17492.2977907421 17492.297790742100000

Float to string conversion examples, style 0-3 and decimal

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo..#TEST_FLOAT') IS NOT NULL DROP TABLE #TEST_FLOAT;

GO

SELECT TOP 10

CONVERT(FLOAT,SQRT(ABS(CHECKSUM(NEWID()))),0) AS FLOAT_NUM

INTO #TEST_FLOAT

FROM sys.all_columns SAC;

SELECT

TF.FLOAT_NUM

,CONVERT(VARCHAR(40),TF.FLOAT_NUM,0) AS FLOAT_CHAR_STYLE_0

,CONVERT(VARCHAR(40),TF.FLOAT_NUM,1) AS FLOAT_CHAR_STYLE_1

,CONVERT(VARCHAR(40),TF.FLOAT_NUM,2) AS FLOAT_CHAR_STYLE_2

,CONVERT(VARCHAR(40),TF.FLOAT_NUM,3) AS FLOAT_CHAR_STYLE_3

,CONVERT(VARCHAR(40),CONVERT(DECIMAL(38,15),TF.FLOAT_NUM,0),0) AS FLOAT_DECIMAL_CHAR

FROM #TEST_FLOAT TF;

Output

FLOAT_NUM FLOAT_CHAR_STYLE_0 FLOAT_CHAR_STYLE_1 FLOAT_CHAR_STYLE_2 FLOAT_CHAR_STYLE_3 FLOAT_DECIMAL_CHAR

----------------- ------------------- ------------------- ----------------------- ------------------- -----------------------

23382.1526382838 23382.2 2.3382153e+004 2.338215263828376e+004 23382.2 23382.152638283755000

31993.3828627108 31993.4 3.1993383e+004 3.199338286271085e+004 31993.4 31993.382862710845000

43482.2643844591 43482.3 4.3482264e+004 4.348226438445909e+004 43482.3 43482.264384459093000

42386.3374095946 42386.3 4.2386337e+004 4.238633740959461e+004 42386.3 42386.337409594613000

13371.3925976317 13371.4 1.3371393e+004 1.337139259763171e+004 13371.4 13371.392597631706000

39725.5073221224 39725.5 3.9725507e+004 3.972550732212239e+004 39725.5 39725.507322122394000

33049.8977154242 33049.9 3.3049898e+004 3.304989771542417e+004 33049.9 33049.897715424173000

21167.5444017486 21167.5 2.1167544e+004 2.116754440174864e+004 21167.5 21167.544401748637000

36290.2935231998 36290.3 3.6290294e+004 3.629029352319983e+004 36290.3 36290.293523199834000

17492.2977907421 17492.3 1.7492298e+004 1.749229779074207e+004 17492.3 17492.297790742072000

akanksha.dixit SSC Enthusiast Points: 102 More actions · Answer 4

akanksha.dixit

SSC Enthusiast

Points: 102

August 6, 2015 at 1:50 am

#1817774

thanks @arvind