I'm old school - like never take anything for granted. So when I move data around in SQL I always check to make sure the data conforms to whatever assumptions are expected.
So if I'm reading data from a file and one of the columns is supposed to be numeric I read the data as text then subject it to testing to verify it is numeric before accepting it as such. This avoids errors and headaches and allows me to notify the users that there is problems in the data.
Normally in SQL you might be inclined to use the ISNUMERIC function, but don't be misled - it's not up to the task.
Here is a small snip of code that will discern numeric from non-numeric. The value shown will yield the error condition. You can play with this to see if it will work for you.
declare @strnum varchar (20)
set @strnum = ',11.25'
if @strnum NOT LIKE '%[^0-9.-]%'
select cast(@strnum as real)
else
select 'error data is non-numeric'
Note that this will flag any comma-separated data. This means a number like 1,234.56 will be flagged as erroneous.
