Why is the dot or period ('.') treated as numeric in SQL Server?

Question

I was working on a query and found that the ISNUMERIC function in SQL Server is returning 1 for the input '.'

Why would that happen? Why does SQL Server treat that as a numeric?

Am I missing something here?

DECLARE @Input VARCHAR = '.'
SELECT IsNumeric(@Input)

The code for `ISNUMERIC` is notoriously slapdash. `$`, `-` and even `$-.` are all considered "numeric" as well, despite none of them containing even a single digit (or necessarily being parseable as an instance of any numeric type). — Jeroen Mostert, Jul 23 '18 at 12:46
I tend to avoid ISUMERIC. Better/safer results with try_conver() or try_cast(). Take a peek at https://www.red-gate.com/simple-talk/blogs/time-stop-using-isnumeric/ — John Cappelletti, Jul 23 '18 at 12:47
@JohnCappelletti I know :( I wasted a couple of hours to figure where is the query going wrong! :| — Praneet Nadkar, Jul 23 '18 at 12:50
As given on MSDN docs this is faulty function [https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-2017] — DhruvJoshi, Jul 23 '18 at 12:50
@JeroenMostert - no, anything that causes `ISNUMERIC` to return `1` will be parsable by *at least one* of the numeric types. The problem is that it doesn't tell you which types would work. All of your examples (and the OPs) can be converted to `money`. — Damien_The_Unbeliever, Jul 23 '18 at 13:01
@DhruvJoshi - it's not "faulty". It does precisely what it's described as doing. The problem is that the question it provides an answer for is not one that can be put to practical use. Nobody wants to know whether a string can be converted to *any* of the numeric data types, which is what `ISNUMERIC` answers. They almost always care whether it can be converted to a *specific* type (or a smaller range of types than all numerics), but that was never `ISNUMERIC`s design. — Damien_The_Unbeliever, Jul 23 '18 at 13:07

Ilyes · Answer 1 · 2018-07-23T13:07:32.947

2

From the docs online:

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols...

For example:

SELECT ISNUMERIC(+)

Will return 1, So I suggest to use TRY_PARSE() instead.

You can read this useful link from MSDN blogs, and this one from SQLServerCentral too.

edited Jul 23 '18 at 13:07

answered Jul 23 '18 at 12:56

Ilyes

14,640
4
29
55

Stanislav Kundii · Answer 2 · 2018-07-23T13:13:54.423

SQL implicit convert empty value to zero.

SELECT CAST('' as int)  
SELECT CAST('.' as money)

and

SELECT ISNUMERIC(0) --> 1
SELECT ISNUMERIC(0.0) --> 1

But i don't know where is this in the documentation

char and varchar

Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators, such as the thousands separator in 123,456.00, are not allowed in the string.

Why is the dot or period ('.') treated as numeric in SQL Server?

2 Answers2