How would I count the number of digits to the right of the decimal in a floating point column?

Question

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 !

Solution 1

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

Solution 2

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.

Solution 3

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

Solution 4

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

View more solutions

Comments

[user918967]

This concept won't work because the CONVERT(varchar(255), Latitude) chops it off at four decimal places.