Counting Decimal Places in a float field

Question

Post reply

Counting Decimal Places in a float field

hughesj23

Ten Centuries

Points: 1349
More actions
July 3, 2014 at 10:11 am

#397004

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

Use RIGHT and CharIndex and LEN function by converting/casting those fields as varchar and apply your logic....

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

hughesj23 Ten Centuries Points: 1349 More actions · Answer 3

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

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/

hughesj23 Ten Centuries Points: 1349 More actions · Answer 5

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

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/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

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 🙂

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]