# 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

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