MySql procedure IF number is odd or even

Question

My learning mysql and my question is to check what i'm doing wrong here:

I'm trying to make a sql procedure to check if the first number of a number user enters is odd or even but im getting syntax error in line 9 near ';//'

Here's the sql:

MYSQL>
DELIMITER //

CREATE PROCEDURE num()
BEGIN
IF (SELECT LEFT(num,1))=1 OR (SELECT LEFT(num,1))=3 OR (SELECT LEFT(num,1))=5 OR (SELECT LEFT(num,1))=7 THEN
SELECT 'number is odd';
ELSEIF (SELECT LEFT(num,1))=2 OR (SELECT LEFT(num,1))=4 OR (SELECT LEFT(num,1))=6 OR (SELECT LEFT(num,1))=8 THEN
SELECT 'number is even'; 
END IF;

END;//

And here is the CALL of then number for testing:

MYSQL> CALL num(3123123123) 

Any ideas?

Answer 1

what you want to do is a calculation. % 2 will give the remainder of a division by 2. if that remainder is not 0 then it is odd

SELECT IF(LEFT(num, 1) % 2 <> 0, "number is odd", "number is even")

you also want to fix your procedure to something like this

DELIMITER //
CREATE PROCEDURE `num`( IN input_num int, OUT output varchar(200))
    BEGIN
        SELECT IF(LEFT(input_num, 1) % 2 <> 0, "number is odd", "number is even") INTO output;
    END//

you would call it like this.

set @a = '';
call num(333, @a);

select @a;

demo fiddle

Answer 2

If you are using an integer type (bigint, integer, etc) as i you should be able to use

• Odd: (i & 0x1) => 1
• Even: (i & 0x1) => 0

Full statement:

UPDATE `test` SET `d` = IF ( ( `i` & 0x1 ) = 1, 'Odd', 'Even' );

Answer 3

Since MySQL 4.X, the MOD() function has been included with MySQL. If you are evaluating rational numbers as EVEN or ODD, the MOD function could be used.

See: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_mod

The MOD(x, y) function returns the remainder of X divided by Y.

We can simply use this function to determine if a value is EVEN or ODD by passing the number to evaluate as X and 2 as Y.

Consider the following examples, where we evaluate ODD or EVEN using MOD() for X values 8800 and 8801, and 3:

/* Example 1: X=8800, Y=2 */

SELECT MOD(8800, 2);

-- Returns 0 (remainder == 0, so our number is EVEN)

/* Example 2: X=8801, Y=2 */

SELECT MOD(8801, 2);

-- Returns 1 (remainder > 0, so our number is ODD)

/* Example 3: X=3, Y=2 */

SELECT MOD(3, 2);

-- Returns 1 (remainder > 0, so our number is ODD)

You could extend this a bit, and combine with the IF/ELSE flow control modifier function IF() if you prefer slightly different return values.

See: https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html#function_if

The IF(expr1, expr2, expr3) takes 3 arguments. IF() evaluates expr1, and returns expr2 if expr1 evaluates as TRUE. Otherwise, expr3 is returned (there are some caveats, see the MySQL Doc above for further details).

Consider the following examples, where we evaluate ODD or EVEN using MOD() for X values 8800 and 8801 again:

/* Example 1: X=8800, Y=2 */

SELECT IF(MOD(8800, 2)>0,'ODD','EVEN');

-- Returns 'EVEN' (remainder == 0, so our number is EVEN)

/* Example 2: X=8801, Y=2 */

SELECT IF(MOD(8801, 2)>0,'ODD','EVEN');

-- Returns 'ODD' (remainder > 0, so our number is ODD)

Cheers!

Answer 4

Maybe you're after a function not a procedure as I don't see how this returns multiple rows from a table...

http://sqlfiddle.com/#!2/5090b1/4/0

MySQL stored procedure vs function, which would I use when? Read up here on when to use one vs the other...

CREATE Function num(I_Num int)
 Returns char(4) deterministic
 RETURN case when mod(left(I_NUM,1),2)=1 then 'Odd' Else 'Even' END;


select num(123) odd, num(234) even;