What does IsNumeric do?

Question

ISNUMERICreturns 1 when the input expression evaluates to a valid numeric data type; otherwise, it returns 0 ( MSDN link ).

So why the output of this query is 1?

select ISNUMERIC('5d9')

Answer 1

Not sure if the bit shot him !! :-)

ISNUMERIC returns 1 when the input expression evaluates to a real integer, floating point, money, or decimal type

I think that in this case he considers "d" to stand for the decimal and, therefore, considering it to be a number.

, 5e9 , "e" . SQL, . , 5e9 1

EDIT: ( !)

Answer 2

select cast('5d9' as float)

5000000000

d, , , e

Answer 3

ISNUMERIC() .

, , , , , , . . +, -, $ ..

(obviously, they are not random, but if you use ISNUMERIC()for verification, it will be difficult for you to find a case when you want to allow all the possibilities that it allows)

Answer 4

Hexa: 5d9 <==> Decimal: 1497

Answer 5

You will need additional testing to conduct the best test. Consider using the Case statement.

declare @var varchar(32)

select @var = '42e2'

select @var AS InputValue,
       ISNUMERIC( @var ) AS TestResult,
       Case ISNUMERIC( @var )
         When 1
          Then CONVERT( float, @var )
         Else
           0
       End AS SimpleConversion,
       Case ISNUMERIC( @var )
         When 1
           Then
             Case
               When CONVERT( varchar(32), CONVERT( float, @var ) ) = @var
                 Then CONVERT( float, @var )
               Else
                 0
             End
         Else
           0
       End AS IntegerResult


InputValue                       TestResult  SimpleConversion       IntegerResult
-------------------------------- ----------- ---------------------- ----------------------
42e2                             1           4200                   0

(1 row(s) affected)