4

I have table with Latitudes and Longitudes that are stored as floating points. How would I count the number of digits to the right of the decimal in the Latitude column? The data would look something like this:

    DECLARE @MyData TABLE (ID, Latitude float, Longitude float)
    INSERT @MyData

    SELECT 'A', 33.11, -55.2235 UNION ALL
    SELECT 'B', 33.6407760431,-87.0002760543 UNION ALL
    SELECT 'C', 42.2997,-70.9081;

and I would want this in 

    ID    |   LatitudeNumberOfDigits
     A    |    2
     B    |   10
     C    |    4

I was trying to convert it to text and split it up using a . as a delimiter but the CONVERT did not work as anticipated. It rounded to four significant digits

    SELECT ID, Latitude, Longitude, 
    CONVERT(varchar(max),[Latitude]) AS LatText 
    FROM @MyData

Gave me

     ID Latitude    Longitude        LatText
     A  33.11           -55.2235         33.11
     B  33.6407760431   -87.0002760543   33.6408
     C  42.2997         -70.9081         42.2997

Thanks !

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user918967
  • 2,049
  • 4
  • 28
  • 43
  • Since the original design for kilometres was 10,000 km = distance N Pole to Equator, 10,000 km ≈ 90°, so there are about 111 km per degree, so 1×10⁻¹¹ degrees ≈ 111×10⁻¹¹ km ≈ 1110×10⁻¹² km ≈ 1.11×10⁻⁹ km ≈ 1×10⁻⁶ m ≈ 1 µm. It is typically a bit hard to identify which part of which object you are referring to at the micrometre level. In other words, 11 places of decimals is at least 3 too many (8 places would be approximately millimetre), and probably 6 too many (5 places would give you metre accuracy). – Jonathan Leffler Jan 17 '12 at 23:21

6 Answers6

3

Try this (it assumes that your floats will have at most 10 decimal places, otherwise adjust the values in STR).

WITH MyData (ID, Latitude, Longitude)
AS
(
    SELECT 'A', CAST(33.11 AS FLOAT), CAST(-55.2235 AS FLOAT) UNION ALL
    SELECT 'B', 33.6407760431,-87.0002760543 UNION ALL
    SELECT 'C', 42.2997,-70.9081 
)
SELECT ID, Latitude, Longitude, 
    LEN(CAST(REVERSE(SUBSTRING(STR([Latitude], 13, 11), CHARINDEX('.', STR([Latitude], 13, 11)) + 1, 20)) AS INT)) AS LatText  
FROM MyData

As others have note, however, FLOATs are likely to give you some headaches. Consider this for instance:

SELECT STR(33.11, 20,17) -- result: 33.1099999999999990
Paolo Falabella
  • 24,914
  • 3
  • 72
  • 86
1

Cast as numeric(maxprecision, maxscale). Then iterate with a case statement.

declare @value as numeric(19,5) = 123.12647

select 
CASE WHEN @value = round(@value,0) then 0
     WHEN @value = round(@value,1) then 1
     WHEN @value = round(@value,2) then 2
     WHEN @value = round(@value,3) then 3
     WHEN @value = round(@value,4) then 4
     else 5 end
Bob Brown
  • 11
  • 1
1

You don't. The number of decimal places is determined by the size of a float in SQL. How many you SEE is determined by the way the float is formatted.

Steve Wellens
  • 20,506
  • 2
  • 28
  • 69
1

Use DECIMAL not FLOAT:

DECLARE @a FLOAT = 33.6407760431
SELECT CAST(@a AS VARCHAR(100))

DECLARE @b DECIMAL(22,10) = 33.6407760431
SELECT CAST(@b AS VARCHAR(100))

Returns:

33.6408

33.6407760431
naiem
  • 437
  • 4
  • 11
0

This may not be valid tsql, but it will be close:

SELECT LENGTH(CONVERT(varchar(255), Latitude - CAST (Latitude as int))) AS decimal_place_count
Bohemian
  • 412,405
  • 93
  • 575
  • 722
0

Use the STR function instead of CONVERT or CAST. It allows you more control on the number of decimals and the total length of the string. See STR (Transact-SQL)

LEN(RTRIM(REPLACE(STR(Latitude,15,10),'0',' '))) - 5
Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188