SQL with Manoj

–> Question:

Has anyone seen this SQL bug before?

If you use the IsNumeric function with 12345 and replace number 3 with an “e” and no other letter, SQL still thinks it’s numeric.

If ISNUMERIC('12e45') = 1
print 'Is Numeric'
else 
Print 'No'

–> My Answer:

Yes, “12e45” is still numeric, notice the small ‘e’ which is a symbol for exponent for representing a big number.

But yes, NUMERIC() function does results incorrect results for some values, like:

SELECT
     ISNUMERIC('123') as '123'
    ,ISNUMERIC('.') as '.' --Period
    ,ISNUMERIC(',') as ',' --Comma

SELECT
     ISNUMERIC('123') as '123'
    ,ISNUMERIC('-') as '-'
    ,ISNUMERIC('+') as '+'
    ,ISNUMERIC('$') as '$'
    ,ISNUMERIC('\') as '\'

… gives you 1 for all these non-numeric values.

After release of SQL Server 2012 and ahead you must be using TRY_PARSE() instead of ISNUMERIC().

Check my blog post on how to use this – Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012
 

–> Answer by CELKO:

The use of E or e for floating point numbers started in the 1950’s with FORTRAN and later in Algol. You wrote “twelve times ten to the forty-fifth power” in the standard notation. The only difference is that some languages have to start with a digit and some can start with the E.

Please look up how floating numbers are displayed. This has been true for over 60 years! The E or e is for “exponent” in the notation that goes back to FORTRAN I.
 

–> Answer by Erland Sommarskog:

In addition to other posts, on SQL2012 or later, you can use try_convert to check if the number is convertible to a specific data type:

SELECT 
CASE WHEN try_convert(int, @str) IS NOT NULL 
       THEN 'Converts' 
       ELSE 'Not convertible' 
END

–> Answer by Dan Guzman:

ISNUMERIC isn’t particularly useful for the common use case of checking for a string containing only the digits 0 through 9. Consider a CASE expression instead to get the desired behavior:

CASE WHEN @value LIKE '%[^0-9]%' OR @value = '' THEN 0 ELSE 1 END

Ref link.