-1

I've been searching for converting my exponential number string into an exact number. So, I've an exponential number stored as string in MySQL. I want to convert back this string in the exact number. But It seems the number is quite big and crosses the boundary and provide me wrong data.

While I tried following code in various format but result is not in proper format.

policy_number = "2.9992020830803E+18";  
number_format($policy_number, 0,'.','');  
// Output  
 2999202083080300032  


(float) $policy_number;  
// Output
2.9992020830803E+18  


sscanf($policy_number, "%f")[0];  
// Output
2.9992020830803E+18  


floatval($policy_number);   
// Output
2.9992020830803E+18  


gmp_init("2.9992020830803E+18");  
gmp_strval($policy_number);  
// Output
0  


"2.9992020830803E+18" + 0;  
// Output
2.9992020830803E+18

Please show me the right way to convert it.

elixenide
  • 44,308
  • 16
  • 74
  • 100
Arun Mahajan
  • 41
  • 5
  • 2
    So, what exactly are you *trying* to get as output? If you stored a large integer as a number when it should have been a string—which I'm guessing you did, based on the variable name—you *can't* "convert back" to the integer reliably. You lost information when you stored it as a numeric format. – elixenide Aug 01 '18 at 04:36
  • I was trying to get 2999202083080300000. If you see in my first attempt it gave an extra 32 don't know why? – Arun Mahajan Aug 01 '18 at 05:35
  • 1
    If you are always going to want the number to end with zeros, then converting it to a string is trivial. But, as I’ve already suggested, the real solution here is to store the data as a string, not a number. After all, you’re not interested in an *amount* if this is an identifier number; you’re interested in a particular *string of digits*. You should store the data accordingly. – elixenide Aug 01 '18 at 05:47
  • Thanks for your reply. I am bit confuse here. See, I've this policy_number already stored in MySQl which is a Varchar. ( I can't change this into any other data type as there is a possibility that it may have char as well in the policy number). Now I am trying to retrieve it from Mysql and would like to showcase on HTML. As number is already in exponential format in MYSQL, I tried to convert using number_format function. As I am using PHP, believe it will take care about data type itself. As far as MySQL is concern I can't change the value input as it is already in exponential format. – Arun Mahajan Aug 01 '18 at 06:38
  • 1
    Varchar is a reasonable column datatype. Your problem appears to be that you are accidentally converting it to a numeric value either when saving to the database or when retrieving it. It’s impossible to say which because you haven’t provided any code. In any case, this is a clear instance of an [XY problem](http://meta.stackexchange.com/q/66377/238426); the real problem you have to solve is not converting a number in exponential notation to a simple integer, but avoiding putting your data into that format in the first place. – elixenide Aug 01 '18 at 06:47
  • OK Thanks for your support. Will look into the input data. But why number_format function gives that extra 32 in last? Is it because of data range issue or something else? – Arun Mahajan Aug 01 '18 at 07:00
  • Please see my updated answer. – Joseph_J Aug 01 '18 at 07:23
  • @ArunMahajan It’s just how math with floating point values works. There’s inherent imprecision in their storage and use. See https://stackoverflow.com/questions/588004/is-floating-point-math-broken – elixenide Aug 01 '18 at 09:11

1 Answers1

1

Updated

I agree with Ed Cottrell that your problem is how you are storing your data in the db. However, if this is a project where you are already looking at a large set of data that is already stored as an exponent then this function I wrote should work for you. It should work for positive and negative bases and exponents. It basically mimics the way you would do the operation by hand.

I was not able to figure out a way to do it using math functions. If someone knows how to do it better please post. In the meantime, I had fun writing this.

Hope it helps you out!

function getRealNumber($number){

  //Parse the base and exponent.
  preg_match('/^(.*?)E[\-|\+](.*?)$/', $number, $data);

  $base = $data[1];
  $exp = $data[2];

  //Test to see if the base is negative.
  if(preg_match('/\-/', $base)){

    $base = str_replace('-', '', $base);
    $isNegative = TRUE;

  }

  //Capture the offset of the decimal point.
  preg_match('/\./', $base, $position, PREG_OFFSET_CAPTURE);

  $offset = $position[0][1]; //This is the offset of the decimal point.
  $string = str_replace('.', '', $base); //Get numbers without decimal.
  $length = strlen($string); //Get the length of string.

  //Test to see if we are adding zeros to the end or beginning of string.
  if(preg_match('/E\+/', $number)){

    //Let's move the decimal.
    if($length > ($exp + $offset)){

      $string = substr_replace($string, '.', ($exp + $offset), 0);

    } else {

      $string = $string . str_repeat('0', $exp - ($length - $offset));

    }


  }elseif(preg_match('/E\-/', $number)){

    //Calculate the number of zeros needed to add and append them.
    if($offset > $exp){

      $string = substr_replace($string, '.', $offset, 0);

    } else {

      $string = '0.' . str_repeat('0', $exp - $offset) . $string;

    }

  }

  //Add the negative sign if we need to.
  if(!$isNegative){

    return $string;

  } else {

    return '-' . $string;

  }

}

$policy_number = "2.9992020830803E+18";
echo getRealNumber($policy_number);

//Will output 2999202083080300000
Joseph_J
  • 3,654
  • 2
  • 13
  • 22