Solved

# MSSQL 2005 - Decimal column query

Posted on 2007-12-04
Medium Priority
795 Views
Hi,

select mydecimalcolumn
from mytable

gives me:
10.05
4.25
4.5
4.0

How can I make a query that gives me data having a fracture more than 1 letter
10.05
4.25

Thank you.
0
Question by:Endelm

LVL 18

Expert Comment

ID: 20401905
You can convert it into a char and select only those records whose string-length following the decimal point (.) is greater than 1.
0

LVL 5

Accepted Solution

nicolasdiogo earned 2000 total points
ID: 20401907
you can use a convert statement for that:

declare @d decimal(10,6)

set @d = 13.2452435

select @d
select convert( decimal (10, 2), @d )
0

LVL 23

Expert Comment

ID: 20401920
Try this.

select mydecimalcolumn
from mytable
where mydecimalcolumn*10 <> Round(mydecimalcolumn*10,0)
0

LVL 25

Expert Comment

ID: 20401938
select mydecimalcolumn
from mytable
where (mydecimalcolumn * 100) % 10 <> 0
0

LVL 25

Expert Comment

ID: 20401942
take that last one back. try this

select mydecimalcolumn
from mytable
where cast(mydecimalcolumn as decimal(10,1) <> mydecimalcolumn)

one op only, which is cast to 1dp, so should be quite fast
0

LVL 6

Expert Comment

ID: 20401973
Based On Imitchie's code,
select mydecimalcolumn
from mytable
where cast(mydecimalcolumn as decimal(10,1) < mydecimalcolumn)
0

LVL 6

Expert Comment

ID: 20401984
sorry the mistake. i am take back my post
0

LVL 5

Expert Comment

ID: 20410157
select mydecimalcolumn from Mytable
where len(mydecimalcolumn) - charindex('.', mydecimalcolumn) >= 2
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.