August 6, 2015 at 12:55 am
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
August 6, 2015 at 1:01 am
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))
August 6, 2015 at 1:25 am
Tons of thanks
it works very well 🙂
August 6, 2015 at 1:31 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply