Home > SQL Server Questions > Bug with ISNUMERIC() – MSDN TSQL forum

Bug with ISNUMERIC() – MSDN TSQL forum

–> 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'

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

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.

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

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

Categories: SQL Server Questions
1. March 7, 2016 at 8:43 pm

12e45 = 1.2e46 (= 1.2E+46) – Exponential notation of a number

• March 8, 2016 at 10:55 am
{"ft_lang_label":"__label__en","ft_lang_prob":0.78738075,"math_prob":0.8191694,"size":2137,"snap":"2022-05-2022-21","text_gpt3_token_len":576,"char_repetition_ratio":0.12751992,"word_repetition_ratio":0.0,"special_character_ratio":0.2915302,"punctuation_ratio":0.10297483,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.97104216,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2022-01-25T11:18:01Z\",\"WARC-Record-ID\":\"<urn:uuid:c569f76f-47ba-4ae8-9f29-0960666b67cd>\",\"Content-Length\":\"213637\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:0b8dafb8-7150-4eb5-803b-2b28f3c0b7e9>\",\"WARC-Concurrent-To\":\"<urn:uuid:c20f242e-0b0b-4e2d-ac0b-fb4f0dffc7e8>\",\"WARC-IP-Address\":\"192.0.78.24\",\"WARC-Target-URI\":\"https://sqlwithmanoj.com/2015/07/08/bug-with-isnumeric-msdn-tsql-forum/\",\"WARC-Payload-Digest\":\"sha1:L525AAA3TANHWF3ZC6GCBNS52RCEJT4O\",\"WARC-Block-Digest\":\"sha1:KXTDHLILN4P5ALYUIOUHMCSWIVZY57DS\",\"WARC-Identified-Payload-Type\":\"application/xhtml+xml\",\"warc_filename\":\"/cc_download/warc_2022/CC-MAIN-2022-05/CC-MAIN-2022-05_segments_1642320304810.95_warc_CC-MAIN-20220125100035-20220125130035-00427.warc.gz\"}"}