Decimals 1

[SQLSister]

OK, I'm drawing a blank here. I have a table with latitude and longitude decimal values from many disparate sources. Unfortunately, all of them are not good numbers. Some have no decimal places which messes up my distance formula. I need to find the records which have no digits after the decimal place to delete the incorrect records. Any ideas on how I will identify them? Table is called CityLocations and the field names are Latitude and Longitude. They are decimal (10,3) data types.

 

[JamesLean]

Are you just trying to find the rows with no decimal part eg 134.000, 3.000? If so then this should do it:

SELECT col_name
FROM citylocations
WHERE RIGHT(CONVERT(varchar(10), col_name), 3) <> '000'

--James

 

[JamesLean]

Are you just trying to find the rows with no decimal part eg 134.000, 3.000? If so then this should do it:

SELECT col_name
FROM citylocations
WHERE RIGHT(CONVERT(varchar(10), col_name), 3) = '000'

--James

 

[SQLSister]

See I knew it was simple, just not thinking today. I really shouldn't install software and try to program at the same time; it messes with my attention span. Thanks.

 

[tlbroadbent]

How about dealing with the numeric columns as numbers rather than strings? You can compare the integer portion of the number to the number to find those with non-zero decimal portions.

Select *
From CityLocations
Where Cast(Latitude As Int) = Latitude
Or Cast(Longitude As Int) = Longitude

I have to know how numbers with 0 in the decimal portion can mess up calculations. Whole numbers should be valid for latitude and longitude measurements. At least I can't see why handling 123.000 would be any different than 123.503. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLSister]

Because one of the input sources rounded some of them (because they took unfiltered user input) and therefore they are no longer in the right location. The other sources do not contain many values which end in .000 (50 out of over 200,000). Therefore when I average all the location values I have for a particular city to get an approximate location of the city business district, these numbers are pulling it away from the true number. And to make it worse some are far enough off they make the average end up not located in any of the city as far as all the rest of the numbers are concerned. I have enough values I can afford to throw out some good ones to get rid of the bad ones, but too many to go through by hand and see which ones are obviously wrong.

 

[tlbroadbent]

SQLSister,

Thanks for the explanation. I didn't understand from your first post why you needed to toss out .000 numbers. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm