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?
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.
I think you are looking for something like this:
select case isnumeric('a') when 1 then convert(int,'a') else null end
Could you explain your goal? Something like this could be useful:
SELECT CASE ISNUMERIC(col)
WHEN 1 THEN CAST(col as float) -- or int, decimal, etc.
ELSE NULL -- Or 0, -9999, or whatever value you want to use as "exception" value
END
The TRY…CATCH construct cannot be used in a user-defined function.
http://msdn.microsoft.com/en-us/library/ms175976%28v=SQL.105%29.aspx
If you are working on SQL Server 2012, I recommend using TRY_CONVERT:
select TRY_CONVERT(int, '.') returns NULL instead of an error.
