What's the best way to skip past casting errors during SQL update and continue?

I have a varchar column in a table where I want to insert its data into another column in the same table where its data type is money. Some of the values in the source column are large numbers that I want to ignore and just want to set these values to NULL.

I tried this:

UPDATE MyTable SET destCol = CASE WHEN IsNumeric(sourceCol) = 1 THEN sourceCol END

But this throws an 'Arithmetic overflow error...' when it tries to test the large numbers.

I'm thinking I might need some sort of function or use a try/cat开发者_开发百科ch block in a stored procedure to get the functionality I want.

UPDATE

I forgot to mention some of the values in the source column have text values that cannot be converted to money at all, these would need to be set to NULL as well.

If the problem is caused by huge numbers, you can limit to those that are convertable to money

UPDATE MyTable
SET destCol = CAST(sourceCol AS money)
WHERE  sourceCol < 922337203685477.5807

money range is money from -922,337,203,685,477.5808 to 922,337,203,685,477.5807

[EDIT]

So you have varchar column. You can do something like this:

UPDATE MyTable
SET destCol = 
    CASE WHEN Len(sourceCol) < 15 AND ISNUMERIC(sourceCol) = 1 THEN CAST(sourceCol AS MONEY) ELSE 0 END

Assuming you just want to convert simple numbers, then something like:

UPDATE MyTable SET destCol = CASE WHEN not sourceCol like '%[^0-9]%' and LEN(sourceCol) <= 8 THEN sourceCol END

This will attempt to update destCol with any string of up to 8 digits. If you need to allow currency symbols or thousands or decimal separators, you'd need to include those also in the LIKE expression.

The problem, as always, with ISNUMERIC is it answers a question that no-one has any use for (can this string be converted to any of the numeric types?), rather than something useful (can this string by converted to numeric type 'X'?)