locked
Converting string to float unique case RRS feed

  • Question

  • Hi TSQL,

    I have unique case where i have nvarchar field with value of '1.376000000000000e+001', i need to convert this value into float and move to another field of float data type. The result should be 13.76

    I apprciate the help in advance!

     

    Thursday, September 6, 2012 3:20 AM

Answers

  • Try

    declare @t table (val nvarchar(100))
    insert into @t values ('1.376000000000000e+001')
    
    select val, CONVERT(float, val) as flVal from @t 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by S_Surve Thursday, September 6, 2012 4:15 AM
    • Marked as answer by Farrukh Lala Thursday, September 6, 2012 6:14 PM
    Thursday, September 6, 2012 3:24 AM
  • Hi, you should be able to perform the conversion using the CONVERT statement.

    Ex:

    SELECT CONVERT(float, nvarcharColumnName)
    FROM TableX

    Thanks,
    Sam Lester (MSFT)

    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Proposed as answer by S_Surve Thursday, September 6, 2012 4:15 AM
    • Marked as answer by Farrukh Lala Thursday, September 6, 2012 6:14 PM
    Thursday, September 6, 2012 3:42 AM

All replies

  • Try

    declare @t table (val nvarchar(100))
    insert into @t values ('1.376000000000000e+001')
    
    select val, CONVERT(float, val) as flVal from @t 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by S_Surve Thursday, September 6, 2012 4:15 AM
    • Marked as answer by Farrukh Lala Thursday, September 6, 2012 6:14 PM
    Thursday, September 6, 2012 3:24 AM
  • Try

    cast(@num1 as decimal(18,2))


    Many Thanks & Best Regards, Hua Min

    • Proposed as answer by S_Surve Thursday, September 6, 2012 4:15 AM
    Thursday, September 6, 2012 3:29 AM
  • Hi, you should be able to perform the conversion using the CONVERT statement.

    Ex:

    SELECT CONVERT(float, nvarcharColumnName)
    FROM TableX

    Thanks,
    Sam Lester (MSFT)

    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Proposed as answer by S_Surve Thursday, September 6, 2012 4:15 AM
    • Marked as answer by Farrukh Lala Thursday, September 6, 2012 6:14 PM
    Thursday, September 6, 2012 3:42 AM
  • Naomi you rock!
    Thursday, September 6, 2012 6:14 PM
  • >> I have unique case where I have NVARCHAR field [sic: columsn are not fields] with value of '1.376000000000000e+001', I need to convert this value into FLOAT and move to another field [sic] of FLOAT data type. The result should be 13.76 [sic: that is a display format, not how it is kept in the database]. <<

    The short answer:
     DECLARE col_2 FLOAT;
     SET col_2 = CAST ('1.376000000000000e+001' AS FLOAT);

    The better answer is do not use the old CONVERT(); we have ANSI Standard syntax today. But why are you using FLOAT? 50 years ago when I write FORTRAN, we had to use it. Today we have DECIMAL(s,p) and none of the math problems.  We also do not car about the display format in the database.  This is as silly as the guys who think that dates are kept in strings in their local dialect; they do not understand how a column is abstract and not a field, which is concrete. 

    I would have used: 

    DECLARE col_2 DECIMAL(5,2);
    SET col_2 = CAST ('1.376000000000000e+001' AS DECIMAL (5,2));

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, September 6, 2012 11:01 PM