July 3, 2014 at 10:11 am
I have a table with three columns: UniqID, Latitude, and Longitude.
I need to write a query to identify when the latitude has more than 6 decimal places past the decimal. Same with Longitude. Values in these attributes can be a negative number. These fields are FLOAT.
Can someone assist?
July 3, 2014 at 10:19 am
Use RIGHT and CharIndex and LEN function by converting/casting those fields as varchar and apply your logic....
July 3, 2014 at 10:24 am
Pulivarthi Sasidhar (7/3/2014)
Use RIGHT and CharIndex and LEN function by converting/casting those fields as varchar and apply your logic....
Try converting FLOAT to VARCHAR and you will find that it is not straight forward thing....
Other way of doing it:
declare @table table (UniqID int, Latitude float, Longitude float)
insert @table select 1, 1.123123,2.123123
insert @table select 2, 1.1231201,2.123123
insert @table select 3, 1.123123,2.1231201
insert @table select 3, 1.123123001,2.123123001
select *
from @table
where FLOOR(Latitude * 1000000 ) != Latitude * 1000000
select *
from @table
where FLOOR(Longitude * 1000000 ) != Longitude * 1000000
July 3, 2014 at 10:29 am
Try converting FLOAT to VARCHAR and you will find that it is not straight forward thing....
Yea, what he said.
I'll give that last code a try. Thanks.
July 3, 2014 at 10:40 am
A little late to the party since Eugene already posted a workable solution. But since I came up with another way of doing this I figured I would post it. 😉
create table #Something
(
UniqID float
)
insert #Something
select 123.45678912 union all
select -45.384756383 union all
select 3.5847 union all
select -867.364
select UniqID
, cast(UniqID as varchar(50)) --This does NOT work with floats
, case when cast(uniqID as numeric(20, 6)) = UniqID then 'Shorter than 6' else 'Longer' end
from #Something
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 3, 2014 at 10:47 am
Thank you Sean! I like that one the best. I could wrap my mind around that one a little easier and it works great. I appreciate it.
July 3, 2014 at 10:56 am
hughesj23 (7/3/2014)
Thank you Sean! I like that one the best. I could wrap my mind around that one a little easier and it works great. I appreciate it.
You're welcome. Either approach will work. Eugene used a little math to move the decimal point and then floor to turn it into a int.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 3, 2014 at 11:00 am
hughesj23 (7/3/2014)
Thank you Sean! I like that one the best. I could wrap my mind around that one a little easier and it works great. I appreciate it.
I agree with you with small reservation:
It will work for FLOAT's with precision of less than 38 as it's maximum handled by numeric datatype. For example value of 12345678901234567890123456789012345.364 (float(53)) would cause overflow.
Using FLOOR it would still work...
Why such big number would you ask?
Because that is about only a case where I would use float datatype, otherwise it is pain in you know where...
If you can, replace them with decimal/numeric and your life will be much much brighter 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply