Catch exception with isnumeric in sql server

Question

I have this possible values in a column

1
65
5 excellent
54
-1
-
.

If I use isnumeric with the last example I get 1, but when I try to convert to number I got an error. I want to use a try-catch in a function but I can't, how can I deal with this?

Answer 1

By the way, an even worse example is '-.', which isnumeric() considers to be valid.

My advice is to look for at least one digit in the value as well. Yucky, but:

isnumeric(val) and val like '%[0-9]%'

Note that isnumeric() also considers something in exponential notation to be valid. So '8e4' will test as positive. This may not be an issue for you, because it will convert to a valid value. Such matches have caused a problem for me in the past, so I tend to use something like:

val not like '%[^0-9.]%' and val not like '%.%.%' and val like '%[0-9]%'

That is, it only has decimal points and digits. And, it doesn't have two decimal points. But, it only works for positive values.