float value converting to exponential while inserting to varchar field

  • 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

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

  • Tons of thanks

    it works very well 🙂

  • 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

  • thanks @arvind

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

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