Even or odd in SQL

Question

This is table structure

I need result like this

id  even  odd
1   0     1
2   1     0
3   0     1
4   1     0
5   0     1
6   1     0

I tried

select id %2=0 then 1 else 0 end or id%2 <>0 then 1 else 0 odd 
from table

Have a look at the [CASE statement](http://technet.microsoft.com/en-us/library/ms181765.aspx). — Tetrinity, Feb 24 '14 at 12:45

score 11 · Accepted Answer · answered Feb 24 '14 at 12:50

11

How about

select 
  id, 
  ~id & 1, 
  id & 1 
from t

answered Feb 24 '14 at 12:50

Alex K.

171,639
30
264
288

2

If a number's low bit is set its odd, else its even. (http://stackoverflow.com/a/5700927/246342) The `~` is simply a bitwise negation to invert the 1/0 to get the correct result. – Alex K. Feb 24 '14 at 13:18

score 3 · Answer 2 · answered Feb 24 '14 at 12:44

Take a look at the CASE keyword. It works very similarly to what you're trying to do in your SELECT statement. In addition, if you want to select multiple columns, separate them with a comma. The OR keyword is used for combining logical conditions in your query, not for specifying multiple columns.

An example of how you could use CASE in your query would be as follows:

SELECT id,
       CASE WHEN id %2=0 THEN 1 ELSE 0 END AS Even,
       [column2]
FROM   [TableName]

score 1 · Answer 3 · answered Feb 24 '14 at 12:44

1

The table structure is just Id?

you could try this!

 select *,
    case when id %2=0 then 1 else 0 end as even,
    case when id %2 <>0 then 1 else 0 end as odd
    from table

answered Feb 24 '14 at 12:44

vhadalgi

7,027
6
38
67

score 1 · Answer 4 · answered Feb 24 '14 at 12:46

You have the right idea, but your syntax is a bit off. I'd use a CASE statement to create the even column, and then a calculate odd accordingly:

SELECT id, even, ABS(even - 1) AS odd
FROM   (SELECT id, CASE WHEN id % 2 = 0 THEN 1 ELSE 0 END AS even
        FROM   my_table)

Even or odd in SQL

4 Answers4