>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?
Try
select * from GPSData where GPS like '%[^0-9]%'
If it's not broken, fix it until it is.
My Blog