How to fetch records with decimal place NOT ZERO

Question

Post reply

How to fetch records with decimal place NOT ZERO

hitesh-733762

SSC Rookie

Points: 43
More actions
June 22, 2012 at 8:30 am

#262763

Assume I have table with col AMOUNT numeric(13,3) with records like this...
AMOUNT
======
100.00
200.00
350.01
235.07
38.10
4.006
500.00
Now I want to fetch only those rows which has decimal value not ZERO. so my result should be (350.01 , 235.07 , 38.10, 4.0006)

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Going to require a table scan to accomplish, but something like this may work:

SELECT

*

FROM

dbo.MyTable

WHERE

Amount - ROUND(Amount,0,1) <> 0.0;

Sean Lange SSC Guru Points: 286609 More actions · Answer 2

Not the best for performance because it isn't sargable but this should work.

;with Nums(Num)

as

(

select 100.00 union all

select 200.00 union all

select 350.01 union all

select 235.07 union all

select 38.10 union all

select 4.006 union all

select 500.00

)

select * from Nums

where Num <> CAST(Num as INT)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

LOL got distracted while posting, seems Lynn beat me to the punch with a different approach to the same thing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Yea, but I think I should change mine to use <> instead of just >.

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

Or a better twist on mine than casting to an int.

select * from Nums

where Num <> floor(Num)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Lynn Pettis

SSC Guru

Points: 442467

June 22, 2012 at 8:50 am

#1504534

Yep, that is another one.

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

where Num % 1 <> 0

OK I am done now...otherwise we are going to start into completely ridiculous string manipulation. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/