Bit rusty on the old sql.
Can you help?
Given a number eg 1 or 2 or 4 I need to determine if it's even or odd nu开发者_运维问答mber and do some calculation depending if even or odd .
How do you detect that in sql (sql server 2000) thanks a lot
Use the modulus operator n % 2
. It returns 0 if the number is even, and 1 if the number is odd.
You can also use sql server BIT WISE operators
DECLARE @Int INT
SELECT @Int = 103
SELECT @Int & 1, @Int % 2
declare @t table(num int) insert into @t select 1 union all select 2 union all select 3 union all select 4
select
num
,case when num % 2 = 0 then 'Even' else 'Odd' end as Status
from @t
Output:
num Status
1 Odd
2 Even
3 Odd
4 Even
e.g. If the number is even(multiply by 1) or odd (multiply by 2) then divide by 10 and get the remainder
declare @myNumber int ,@result int
set @myNumber = 16
select
Result =
(case when @myNumber % 2 = 0 then @myNumber * 1 else @myNumber * 2 end) %10
Result
6
when @myNumber = 11
then
Result
2
Hope this helps
I am using the same thing in MS SQL SP as follows:
IF @current_number % 2 = 0 SET @something = 1
-- or --
IF @current_number % 2 = 0 exec sp_whatever
Use the modulus operator (%).
x % 2
will tell you if x is even or odd.
NOT INTENDED AS A SERIOUS ANSWER...BUT IT WORKS.
Please reserve the following for giving to people you don't like who simply want a quick answer without wanting to understand the solution (i.e people wanting to cheat).
--Enter the whole integer you want to check
declare @number int = 115
--The check is independent of sign
set @number = abs(@number)
declare @OriginalNumber int = @number
--Firstly, we need to peform the Wilhelm Leibniz conversion, 64 length to allow for very big numbers
declare @WilhelmLeibnizConversion varchar(64) = ''
declare @currentBit int = power(8,exp(log(1))+1)
while @currentBit > 0
begin
set @WilhelmLeibnizConversion=convert(char(1), @number % 2) + @WilhelmLeibnizConversion
set @number = convert(int, (@number / 2))
set @currentBit-=1
end
--Although checking the 1 bit value of the Wilhelm Leibniz conversion is usually enough, for robust code you should also include the Kimmo Eriksson Factors one and two.
declare @KimmoErikssonFactor1 int = (@OriginalNumber + 1) % 2
declare @KimmoErikssonFactor2 int = (@OriginalNumber - 1) & 1
--Now check all 3 for 100% confirmation on the parity of your original number.
select case when right(@WilhelmLeibnizConversion,1) = 0 and (@KimmoErikssonFactor1 + @KimmoErikssonFactor2 <> 0) then 'Even' else 'Odd' end
Let's say for a table STATION.
Schema:
ID NUMBER
CITY VARCHAR
STATE VARCHAR
-- You can use any of the mentioned criteria to fetch the even ID.
-- 1. MOD() Fucntion
select distinct CITY from STATION as st where MOD(st.id, 2) = 0
-- 2. % function
select distinct CITY from STATION as st where st.id % 2 = 0
You could check the 1-bit of the hex value of the number. If that bit is on, then it is odd.
DECLARE @Int INT
SELECT CASE WHEN @Int&0x0001<>0 THEN 'ODD' ELSE 'EVEN' END
The %
operator retrieves the modulus, so checking the modulus of a division by 2 you can know if it is odd or even.
DECLARE @i int =1;
while @i <= 20
begin
if @i % 2 = 0
PRINT @i
IF @i = 10 PRINT 'halfway there!';
set @i = @i+1;
end
USE AdventureWorks;
GO
SELECT BusinessEntityID,
CASE BusinessEntityID % 2
WHEN 0 THEN 'Even' ELSE 'Odd' END AS "Status"
FROM HumanResources.Employee;
GO
精彩评论