Solved

MSSQL 2005 - Decimal column query

Posted on 2007-12-04
8
785 Views
Last Modified: 2008-02-01
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
Comment
Question by:Endelm
8 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
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

by:
nicolasdiogo earned 500 total points
Comment Utility
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

by:Ashish Patel
Comment Utility
Try this.

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

Expert Comment

by:imitchie
Comment Utility
select mydecimalcolumn
from mytable
where (mydecimalcolumn * 100) % 10 <> 0
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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

by:Rajesh_mj
Comment Utility
Based On Imitchie's code,
select mydecimalcolumn

from mytable

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

Open in new window

0
 
LVL 6

Expert Comment

by:Rajesh_mj
Comment Utility
sorry the mistake. i am take back my post
0
 
LVL 5

Expert Comment

by:ursangel
Comment Utility
select mydecimalcolumn from Mytable
where len(mydecimalcolumn) - charindex('.', mydecimalcolumn) >= 2
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
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.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now