What is a correct ISNUMERIC('.') value?

There is no controvercy here. The mere fact that isnumeric returns true does not guarantee that the value is converible to numeric. This is due to the isnumeric implementation, which calls for trying to check whether the input can be represented as a numeric value in any of the available settings. For example, the input 123.456,56 is not convertible to decimal number with en_US settings, but isnumeric for this input is 1 because it happens to be a valid number in any environment where dot is used as a thousand separator and comma as decimal point. Same thing applies to 123 456,56 (isnumeric is 1 because a space is used as a thousand separator in some environments). On the other hand, it is possible to get isnumeric = 0 while the input is convertible. For example

select isnumeric(' ') is_numeric, cast(' ' as int) cast_as_int;

-- returns 
is_numeric  cast_as_int
----------- -----------
0           0

Though isnumeric returns false for the above, it still can be cast as integer (but not as decimal, decimal still errors out). Here is another obscure example:

declare @i varchar(3);
set @i = 1234;

select isnumeric(@i) is_numeric;

-- returns
is_numeric
-----------
0

-- why the input is not numeric? try to see what does it equal to
select @i result;

--returns
result
------
* 

Where did the * come from? Well, it came from internal implementation of the int data type, which returns * if the integer value does not fit into varchar variable :)

The bottom line: any cave man attempts to safely convert input values to decimal if such inputs passed isnumeric validation are failure prone.

<!-- Begin Edit

Here is the sample of such attempt. I will create a table, populate it with few records and then try to sanitize the data and then select it. Possibilities are endless and never bullet proof though :(

create table dbo.DecimalSample 
(
    id int identity(1, 1) not null
       constraint PK_DecimalSample_ID primary key clustered,
    test_result varchar(50) null
);
go

insert into dbo.DecimalSample
select null test_result
union select ' '
union select '.'
union select '123 456,56.77'
union select 'close to 456,789, but I am not sure'
union select 'positive'
union select '.67321'
union select '0.2323232'
union select '5.2'
union select '123,777'
union select ' 15   ';

-- cave man attempt to make it work assuming en-us settings
-- whatever is not convertible is replaced with 0.0000 :)
;with records(id, test_result, sanitized) as
(
    select
        id, test_result, 
        rtrim(ltrim(replace(replace(test_result, ' ', ''), ',', ''))) 
        as sanitized
        from dbo.DecimalSample
)
    select 
        id, test_result,
        case 
            when isnumeric(sanitized) = 0 or sanitized in ('.', '') then 0
            else cast(sanitized as decimal(19, 4))
        end converted
        from records;

-- the above returns:
id          test_result                         converted
----------- ----------------------------------- --------------
1           NULL                                0.0000
2                                               0.0000
3            15                                 15.0000
4           .                                   0.0000
5           .67321                              0.6732
6           0.2323232                           0.2323
7           123 456,56.77                       12345656.7700
8           123,777                             123777.0000
9           5.2                                 5.2000
10          close to 456,789, but I am not sure 0.0000
11          positive                            0.0000

One more thing I would like to point out. Cast and Convert to decimal without specifying precision and scale is not a good idea because default scale is 0 meaning that decimal numbers are going to be converted incorrectly. For example, select convert(decimal, '123.4567') returns 123 and this is probably not what is expected. select convert(decimal(10,2), '123.4567') returns 123.46 with just a small rounding error and select convert(decimal(10,2), '123.4567') returns 123.4567 not loosing any precision at all.

End Edit -->

Oleg

Just wanted to clarify the difference

ISNUMERIC - is a function that will return true if the expression can be converted to any numeric data type, including: int, bigint, tinyint, decimal, numeric, real, money, smallmoney, float.

This has been discussed in detail at

• http://ask.sqlservercentral.com/questions/1025/bug-in-mssql-function-isnumeric
• http://ask.sqlservercentral.com/questions/608/how-to-select-records-with-letters-in-an-nvarchar-field-that-should-only-be-numbers

Confusingly there is a numeric data type called numeric which is simply a synonym for decimal.
So

SELECT CONVERT(numeric, '.')

is functionally equivalent to

SELECT CONVERT(decimal, '.')

which when written like that is clear why it fails with an error.

Kev Riley has a good idea using PATINDEX here too: Kev's Solution.

(The link was too long for a comment.)

Since a '.' could be a decimal or a period in a sentence, it's hard to say. Maybe it should return NULL since it could be either.

I think it returns 1 so that errors (or false alarms) will not be thrown when evaluating numbers with decimals. I would side with 1 on that count.

p.s., '-' and '+' also return 1.