Find the SQL value behind the decimal

25 pts.
Tags:
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?
ASKED: June 23, 2010  8:42 PM
UPDATED: June 24, 2010  8:09 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

————–

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Tslake99
    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.
    25 pointsBadges:
    report
  • carlosdl
    Just modify the above examples to put the integer check in the WHERE clause.
    SELECT  <some fields>
    FROM <some table>
    WHERE quantity != FLOOR(quantity)
    65,110 pointsBadges:
    report
  • Kccrosser
    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)
    3,830 pointsBadges:
    report
  • Tslake99
    Thank you! That was exactly what I needed!
    25 pointsBadges:
    report
  • Gmalik
    I got my answer 
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following