Hi,

I am trying to check all the values in my table are numeric and I am using ISNUMERIC function for that. But, for the following statements
ISNUMERIC(',') or ISNUMERIC('.') is returning true and I am getting the conversion errors at later stage.

Please suggest me any Solution for this (or) any alternate Ideas? I am using SQL 2000 with SP3.

Hi Ram,
Actually characters like dollar($),comma(,) and dot(.) is used to represent numberic data.
   E.g.
       $5000.00
       5000.75
       56,775.00
thats why these character is treated as numeric data.

To avoid this you can write a query in different way like.......

Suppose we have a table
create table temps
(
fld varchar(100)
)

and data of table like
19
29
23
subodh
.
,
.,
=
$

When you run the query:: select fld,isnumeric(fld) as 'Isnumeric' from temps
it will return:
fld Isnumeric
19 1
29 1
23 1
subodh 0
. 1
, 1
., 1
= 0
$ 1

Obviously the output is not desired.To get the desired result we can write our query like:-
select fld,case
        when fld like '%.%' then 0
        when fld like '%,%' then 0
        when fld like '%$%' then 0
        else isnumeric(fld)
          end as 'IsNumeric'
from temps
it will give correct output:-
fld IsNumeric
19 1
29 1
23 1
subodh 0
. 0
, 0
., 0
= 0
$ 0

On the above concept, Now I hope you can solve your problem.



Enjoy!
Subodh Kumar
Phoneytunes.com

Nope.

Your WHERE criteria would reject a valid numeric string like 19.25 as non numeric.

This is actually an interesting issue. From BOL:

"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".

Apparently, the rule is whether the CAST function would throw an error attempting to convert the string to one of the numeric types. Many seemingly invalid numeric strings in fact result in a ISNUMERIC value of 1. For example, these strings all result in a 1:

.
,
,.
.,
$.
$,.
$,9.
9,9. --!!! this will CAST to money as 99.0000
etc. etc.

because you can CAST each of these strings to a money type and you get a "reasonable" value, e.g. CAST('.' as money) results in a value of .0000.

What's weird is that if you attempt to CAST the same string to any other type (like decimal) you'll get a conversion error. What's even worse, the string '1E10' will return a 1, but a CAST to money will result in a conversion error. Of course, that string will successfully CAST to FLOAT.

Unfortunately, it seems ISNUMERIC doesn't tell you which numeric type it can successfully CAST to, only that it can make some successful CAST...

I think this is a flaw in the definition and behavior of ISNUMERIC, and I have no good solution, short of writing an ugly user defined function which makes use of the LIKE operator and other string operators to parse your string into what you would define as a valid numeric string, and not what SQL Server ISNUMERIC does...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com