How many decimal places are there in a specific column


  • I have to find a way to count the number of decimal places for each row in my column and then sort my the largest value based on the number of decimal places.




    I need the data to look like this after I run the query


    col1                                 DecimalPlaces
    0.290302012 13
    2.239021019 9
    23.2392 5
    1.1 1


    I was trying the len() function but that didn't return the right number of characters.


    Thank You

    Wednesday, June 25, 2008 4:54 PM

All replies

  • example


    Code Snippet
    create table #test(col1 decimal(38,20))
    insert into #test values(2.239021019)
    insert into #test values(1.1)
    insert into #test values(0.43434)
    insert into #test values(1)





    Code Snippet
    select col1, patindex('%.%',reverse(replace(rtrim(replace(convert(varchar(100),col1),'0',' ')),' ','0')))-1
    from #test
    order by 2 desc




    2.23902101900000000000 9
    .43434000000000000000 5
    1.10000000000000000000 1
    1.00000000000000000000 0


    Keep in mind that I don't count trailing zeros, if you want to count those then take out the 2 replace functions



    Denis The SQL Menace

    Wednesday, June 25, 2008 5:10 PM

    Here is a sample, but you have to convert col1 to a character.



    Code Snippet

    Select *,

    len(col1) - charindex('.',Col1) NumberDecimalPlaces



    Select '2.0004' Col1

    Union All

    Select '11.00005' Col1

    Union All

    Select '3.000006' Col1

    ) A



    Wednesday, June 25, 2008 5:12 PM