SQL how to convert varchar to numeric without hanging on CAST error
Posted on 2015-01-26
I want to do the following:
SET Amount = CAST(SrcAmount AS numeric(12,2))
…but I get the error
Error converting data type varchar to numeric
on some value, but they all look valid. This suggests that there’s an obscure, non-printing ASCII character hidden in one of the SrcAmount values.
Is there some way I can modify the SQL command above to ignore the CAST error and simply skip over those records which generate it?