Check if a value is Integer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Check if a value is Integer

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2007-09-19 : 04:47:28

Hello,

How can I check if a calculated value is integer? I mean if the result is 1 or 5 or 22 or 535,.. and not 1,25 or 255,22

For example, something like this:

SELECT column1 = CASE
WHEN ((20 - 6) / 7) Is integer THEN 'YES, IT IS INTEGER'
ELSE 'IS NOT INTEGER'
END..

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 04:54:50

SELECT Col1, FLOOR(Col1) <> CEILING(Col1) THEN 'Decimal' ELSE 'INTEGER' END
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 04:55:34

SELECT Col1, Col2, CASE WHEN Col1 % Col2 = 0 THEN 'INTEGER' ELSE 'DECIMAL' END
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-19 : 05:08:43

Peter, Modulo operator won't work with floating point numbers.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cesark
Posting Yak Master

215 Posts

Posted - 2007-09-19 : 05:09:06

The % operator returns the number of decimals that the result of the division has? If so, I supose that if it is 0 means that no decimal exists in the returned result, so it is an integer. Is this correct?

P.D. I only will use integer values for the calculations

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 05:22:42

quote:
Originally posted by harsh_athalye

Peter, Modulo operator won't work with floating point numbers.

He is not using floating point numbers. He is trying to divide to integer and wants to see if the result is integer too..

Common denominator is the solution to the problem.
If Col1 % Col2 equals 0 it means no remainder is found, hence the division is integer.

E 12°55'05.25"
N 56°04'39.16"

cesark
Posting Yak Master

215 Posts

Posted - 2007-09-19 : 06:34:07

Ok, thank you very much for the solution, which is perfect for my case

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-20 : 02:23:48

Other one though complex

SELECT Col1, Col2, CASE WHEN Col1 -(Col1/Col2)*Col2 = 0 THEN 'INTEGER' ELSE 'DECIMAL' END
FROM Table1

Madhivanan

Failing to plan is Planning to fail

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-20 : 17:31:00

SELECT Col1, Col2, CASE WHEN Col1 -(Col1/Col2)*Col2 = 0 THEN 'INTEGER' ELSE 'DECIMAL' END
FROM Table1

Technically the formula will always resolve to 0 no matter what the numbers are (except where col2 = 0). It only works here because we are dealing with integers and for a select...5/4 = 1 not 1.25. Also works due to rounding (.9 repeating for sql = .9999999999 when it should be 1). Write that formula out on paper and fill it in with any numbers...it will always result to 0. The 1/Col2*Col2 cancel each other out and you are left with Col1-Col1 which = 0

Consider this though:

declare @x decimal(10,4), @y decimal(10,4)

select @x = 4, @y = 2

SELECT @x -(@x/@y)*@y

select @x = 5, @y = 4

SELECT @x -(@x/@y)*@y

Both return 0 but we know that 5/4 has decimal(s).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 04:40:28

quote:
Originally posted by Van

SELECT Col1, Col2, CASE WHEN Col1 -(Col1/Col2)*Col2 = 0 THEN 'INTEGER' ELSE 'DECIMAL' END
FROM Table1

Technically the formula will always resolve to 0 no matter what the numbers are (except where col2 = 0). It only works here because we are dealing with integers and for a select...5/4 = 1 not 1.25. Also works due to rounding (.9 repeating for sql = .9999999999 when it should be 1). Write that formula out on paper and fill it in with any numbers...it will always result to 0. The 1/Col2*Col2 cancel each other out and you are left with Col1-Col1 which = 0

Consider this though:

declare @x decimal(10,4), @y decimal(10,4)

select @x = 4, @y = 2

SELECT @x -(@x/@y)*@y

select @x = 5, @y = 4

SELECT @x -(@x/@y)*@y

Both return 0 but we know that 5/4 has decimal(s).

My answer was based on assumption that they are integers


declare @x int, @y int

select @x = 4, @y = 2

SELECT @x -(@x/@y)*@y

select @x = 5, @y = 4

SELECT @x -(@x/@y)*@y

Madhivanan

Failing to plan is Planning to fail

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-21 : 09:22:24

I understand totally. But you could have 15.5 / 7.75 which is 2...an integer. Just wanted to point that out.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 09:38:31

quote:
Originally posted by Van

I understand totally. But you could have 15.5 / 7.75 which is 2...an integer. Just wanted to point that out.

Agreed

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources