locked
query to convert exponential to number RRS feed

  • Question

  • hi guys,

    i want to query which converts 9.99E125 to number ....can anyone help me

     

    regards,

    latheesh

    Thursday, January 6, 2011 5:50 AM

Answers

  • Hello Peso,

    yes, STR() returns a string, but what else? Latheesh needs a nice formatted output of a FLOAT number, and this output can only be a string.

    As already said, 9.99E125 and CAST(9.99E125) are *identical* FLOAT numbers:  print 9.99E125 - cast(9.99E125 as float) gives 0. 

    Kind Regards,

    Klaus

    • Marked as answer by Ai-hua Qiu Thursday, January 13, 2011 9:02 AM
    Thursday, January 6, 2011 3:38 PM
  • i want to query which converts 9.99E125 to number ....can anyone help me


    Avogadro's number is only 10^23 (E23).

    Is there a real application which uses 10^125?  Number of atoms in the universe? Or just a college exercise?


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    • Proposed as answer by Naomi N Wednesday, January 12, 2011 7:25 PM
    • Edited by Kalman Toth Monday, October 1, 2012 11:01 PM
    • Marked as answer by Kalman Toth Monday, October 1, 2012 11:01 PM
    Wednesday, January 12, 2011 7:20 PM

All replies

  • go through with this link:

    http://www.sqlservercentral.com/Forums/Topic837704-391-1.aspx

     


    --------------------------------- Devender Bijania
    Thursday, January 6, 2011 6:12 AM
  • Hello,

    9.99E125 *is* a FLOAT constant, and is the same as 99900.....0. Thus, CAST(9.99E125 AS FLOAT) is useless. However, TSQL statements like PRINT or SELECT try to output FLOAT values in an as short as possible way, so they switch to E-format depending on the number size.

    You can avoid this by using the TSQL function STR, e.g. PRINT STR(9.99E125, 140, 2), whereas 140 ist the number of digits in total and 2 is the number of decimal places right to the decimal point.

    Unfortunalety, TSQL doesn't include a "real" formatting function like C/C++'s sprintf(), but STR() is enough for your problem.

    Regards,

    Klaus

    • Proposed as answer by Naomi N Friday, January 7, 2011 5:45 AM
    Thursday, January 6, 2011 1:29 PM
  • Hello Peso,

    yes, STR() returns a string, but what else? Latheesh needs a nice formatted output of a FLOAT number, and this output can only be a string.

    As already said, 9.99E125 and CAST(9.99E125) are *identical* FLOAT numbers:  print 9.99E125 - cast(9.99E125 as float) gives 0. 

    Kind Regards,

    Klaus

    • Marked as answer by Ai-hua Qiu Thursday, January 13, 2011 9:02 AM
    Thursday, January 6, 2011 3:38 PM
  • It is not clear what you are trying to achieve.

    If you want to convert the string '9.99E125' to a number so you can do calculations on it, then you can simply cast that value to a float.

    If you want to print the number without the exponential (scientific) notation, then you can cast to Float and then to Decimal for values up to 'E38'. For bigger numbers, you would have to write your own function.


    Gert-Jan
    Thursday, January 6, 2011 6:13 PM
  • Hello Gert,

    it's not necessary to write an own function, STR() is able to handle exponents greater than 38.

    Klaus

    Thursday, January 6, 2011 7:40 PM
  • hi..thanks for your response...i tried Cast(9.99E125 as float) but i am getting result as 9.99E+125....what i actually want is ? i don't need this E(exponential) in my result. i want my result in number.
    latheesh
    Friday, January 7, 2011 4:37 AM
  • hello Kuehne,

    i can't get u clearly, can u please explain your solution in detail......

     

    regards,

    latheesh


    latheesh
    Friday, January 7, 2011 4:41 AM
  • u can try this....

    SELECT convert(numeric(38,0),cast(@t AS float))

    SQL Server Numeric precision limit is 38.  so in '9.99E125' E125 is much more....


    --------------------------------- Devender Bijania
    Friday, January 7, 2011 4:50 AM
  • hello kuehne,

    i used STR() i am getting what i exactly want...thanks for your response.....


    latheesh
    Friday, January 7, 2011 5:18 AM
  • This is an example of Klaus' solution:

    SELECT LTRIM(STR(CAST('9.99E125' AS float),150))
    

    Make sure that the length you use for STR (in this case 150) is big enough to hold the float's value (in this case at least 126).


    Gert-Jan
    Friday, January 7, 2011 7:06 PM
  • i want to query which converts 9.99E125 to number ....can anyone help me


    Avogadro's number is only 10^23 (E23).

    Is there a real application which uses 10^125?  Number of atoms in the universe? Or just a college exercise?


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    • Proposed as answer by Naomi N Wednesday, January 12, 2011 7:25 PM
    • Edited by Kalman Toth Monday, October 1, 2012 11:01 PM
    • Marked as answer by Kalman Toth Monday, October 1, 2012 11:01 PM
    Wednesday, January 12, 2011 7:20 PM