isnumeric with bug?

Question

Execute command bellow in SQl Server 2014.

   select ISNUMERIC('1E234') -- return 1
   select ISNUMERIC('1E2341') -- return 0

I expect that the first select returns 0

My solution:

     -- if return 0 is numeric 
     select PATINDEX('%[^0-9]%','1E234') 
     select PATINDEX('%[^0-9]%','1E2341') 
     select PATINDEX('%[^0-9]%','102341')

Best regards!

isnumeric supports `float` and `real`: https://msdn.microsoft.com/en-CA/library/ms173773.aspx +/- 1e308, basically. `e2341` is outside the allowable range. — Marc B, Aug 29 '16 at 14:55
Duplicate? http://stackoverflow.com/questions/5988939/isnumeric07213e71-true — Tim Schmelter, Aug 29 '16 at 15:01
Thanks @MarcB for explanation about exponecial values in float and real types. — Rodrigo, Aug 29 '16 at 15:10

casraf · Answer 1 · 2016-08-29T15:18:04.510

1e234 is actually a number. It's the scientific notation, meaning: 1.00×10²³⁴. (throw that in a calculator!) Another example to emphasize, would be 3.1e24 which is equal to 3.1×10²⁴.

This is why you get that result: it's correct (up to a certain limit).

3.1E24 => 3,100,000,000,000,000,000,000,904

 1E234 =>   1,000,000,000,000,000,256,144,
          240,016,312,560,912,656,840,512,
          968,136,864,872,232,424,776,040,
          744,208,304,800,656,032,440,648,
          488,920,616,296,544,576,976,448,
          056,376,040,432,472,424,840,856,
          440,904,776,056,632,368,528,312,
          448,144,432,344,224,424,808,952,
          160,472,416,864,520,520,320,112,
          296,192,360,016,000,088,968

1E2341 => too big to calculate!!!

The problem with your solution, is that sometimes e is used for number notations in computer science too, so the e-notation must be taken into account as a working number.

isnumeric with bug?

1 Answers1