Home > IT Answers > Development > Find the SQL value behind the decimal
Tslake99
25 pts.
 Find the SQL value behind the decimal
SQL, SQL Query, SQL Syntax
I need to know if the numbers after a decimal point are greater than 0. What is the easiest SQL syntax to do this?

Software/Hardware used:
ASKED: June 23, 2010  8:42 PM
UPDATED: June 24, 2010  8:09 PM
RELATED QUESTIONS
Answer Wiki:
In other words, you want to know if the number is an integer, right ? The syntax could be different depending on the database being used. Oracle: <pre>SELECT DECODE(yourColumn,FLOOR(yourColumn),'INTEGER','NON-INTEGER') FROM yourTable;</pre> SQL Server: <pre>SELECT CASE WHEN yourColumn = FLOOR(yourColumn) THEN 'INTEGER' ELSE 'NON-INTEGER' END FROM yourTable</pre> --------------
Last Wiki Answer Submitted:  June 23, 2010  10:10 pm  by  carlosdl   63,535 pts.
All Answer Wiki Contributors:  carlosdl   63,535 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

My stockroom wants to know when the system shows that we have a fraction of a quantity in stock. For example, the system shows .333. He wants to be all items that have a fractional amount on a SQL Report. I’m trying to come up with the syntax to analyze each quantity amount and only pull those with fractions. This is a SQL Database.

Thanks in advance.

Tslake99
 25 pts.
 

Just modify the above examples to put the integer check in the WHERE clause.

SELECT  <some fields>
FROM <some table>
WHERE quantity != FLOOR(quantity)
carlosdl
 63,535 pts.
 

A marginally faster way to do this (if you have some really big tables) is:

select <some fields>
from <some table>
where quantity != CAST(quantity as int)
Kccrosser
 3,830 pts.
 

Thank you! That was exactly what I needed!

Tslake99
 25 pts.
 

I got my answer 

Gmalik
 10 pts.