Finding even or odd ID values
SqlSql Server-2008Sql Problem Overview
I was working on a query today which required me to use the following to find all odd number ID values
(ID % 2) <> 0
Can anyone tell me what this is doing? It worked, which is great, but I'd like to know why.
Sql Solutions
Solution 1 - Sql
ID % 2 is checking what the remainder is if you divide ID by 2. If you divide an even number by 2 it will always have a remainder of 0. Any other number (odd) will result in a non-zero value. Which is what is checking for.
Solution 2 - Sql
For finding the even number we should use
select num from table where ( num % 2 ) = 0
Solution 3 - Sql
As Below Doc specify
> dividend % divisor > > Returns the remainder of one number divided by another.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/modulo-transact-sql#syntax
For Example
> 13 % 2 return 1
Next part is <> which denotes Not equals.
Therefor what your statement mean is Remainder of ID when it divided by 2 not equals to 0
Be careful because this is not going to work in Oracle database. Same Expression will be like below.
MOD(ID, 2) <> 0
Solution 4 - Sql
ID % 2 reduces all integer (monetary and numeric are allowed, too) numbers to 0 and 1 effectively.
Read about the modulo operator in the manual.
Solution 5 - Sql
In oracle,
select num from table where MOD (num, 2) = 0;
Solution 6 - Sql
dividend % divisor
Dividend is the numeric expression to divide. Dividend must be any expression of integer data type in sql server.
Divisor is the numeric expression to divide the dividend. Divisor must be expression of integer data type except in sql server.
SELECT 15 % 2
Output
1
Dividend = 15
Divisor = 2
Let's say you wanted to query
Query a list of CITY names from STATION with even ID numbers only.
Schema structure for STATION:
ID Number
CITY varchar
STATE varchar
select CITY from STATION as st where st.id % 2 = 0
Will fetch the even set of records
In order to fetch the odd records with Id as odd number.
select CITY from STATION as st where st.id % 2 <> 0
% function reduces the value to either 0 or 1
Solution 7 - Sql
It's taking the ID , dividing it by 2 and checking if the remainder is not zero; meaning, it's an odd ID.
Solution 8 - Sql
<> means not equal. however, in some versions of SQL, you can write !=