Please ignore, I found the answer: ISNUMERIC(fieldname) = 0
Hi,
I have inherited a database which stores some GPS values in a varchar field. The client wants me to pull out data which is within a certain numeric range. So if the GPS N value is set at 17000, they want to enter a range, say 1000 and then pull out all records within the range 16000 to 18000. All is good as far as that goes.
The problem is that the GPS values are stored in varchar and the is no coding in place to ensure only numeric values are entered so when I try to
cast(gpsn as numeric(15,0))
it fails.
I have found one record where the user entered 'ECCLES RD' and have fixed that, but it is failing on some other record(s).
So far I have selected the distinct GPSN values and ordered them to see if anything jumps out at me when eyeballing it, but nothing.
Does anyone have a suggestion as to how I can find the data that is causing the trouble?