T-SQL's IsNumeric function problem

IsNumeric function in T-SQL can be used to determine if a value is a number.

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

e.g. Select IsNumeric (' . ') returns 1

But if you try to convert it to decimal as

Select convert(decimal(9,2), ' .' ) it returns error " Error converting data type varchar to numeric"

As MSDN documentation (http://msdn.microsoft.com/en-us/library/aa933213(SQL.80).aspx) " ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types."

Work around is to try converting it to money as

select CAST(' .' as money)

select CONVERT (money, ' .')

The result is 0.0

If you need to convert number to decimal to fractions more than money can return then you may need to write user defined function as described at link below to check if number is really numeric.

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html