Solved

# MSSQL 2005 - Decimal column query

Posted on 2007-12-04
785 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

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 500 total points
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

Try this.

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

LVL 25

Expert Comment

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

LVL 25

Expert Comment

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

Based On Imitchie's code,
``````select mydecimalcolumn

from mytable

where cast(mydecimalcolumn as decimal(10,1) < mydecimalcolumn)
``````
0

LVL 6

Expert Comment

sorry the mistake. i am take back my post
0

LVL 5

Expert Comment

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

## Featured Post

### Suggested Solutions

MS Dynamics AX 2009 2012 Database/Report Optimization 3 26
C# SQL BULK INSERT CLASS 5 33
report returning null 21 49
Complex SQL 10 32
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.