none
How many decimal places are there in a specific column

    Question

  • 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.

     

    col1                                
    2.239021019
    1.1
    0.290302012
    23.2392

     

    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

     

     

    output


    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

    http://www.lessthandot.com/

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    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

    FROM

    (

    Select '2.0004' Col1

    Union All

    Select '11.00005' Col1

    Union All

    Select '3.000006' Col1

    ) A

     

     

    Wednesday, June 25, 2008 5:12 PM