Oracle PL/SQL/Numeric Math Functions/MOD

In Oracle, when the second value is a 0, the MOD function returns the first value

 
SQL>
SQL>
SQL> --MOD(1000, 0)
SQL>
SQL> --In Oracle, when the second value is a 0, the MOD function returns the first value
SQL> --(which deviates a bit from the rules of mathematics).
SQL>
SQL> SELECT MOD(1000, 0) AS ZERO_DIVIDE FROM Dual;
ZERO_DIVIDE
-----------
       1000
SQL>

MOD(8, 4)

 

SQL> select MOD(8, 4) from dual;
  MOD(8,4)
----------
         0
SQL>

MOD: If there is no remainder, the function returns a zero

 
SQL>
SQL> --MOD: If there is no remainder, the function returns a zero.
SQL>
SQL> SELECT MOD(2200, 300) AS ODD, MOD(2100, 300) AS EVEN FROM Dual;
       ODD       EVEN
---------- ----------
       100          0

MOD: Returns the remainder of n/m where both n and m are integers

 

SQL>
SQL> -- create demo table
SQL> create table TestTable(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    MyName             VARCHAR2(10 BYTE),
  4    MyDate             DATE,
  5    MyNumber           Number(8,2)
  6  )
  7  /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("2","Jason",to_date("19970622","YYYYMMDD"),-12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("3","Smith",to_date("19980513","YYYYMMDD"),22.1);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("4","Tailor",to_date("19990624","YYYYMMDD"),-2.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("5","Darlene",to_date("20000415","YYYYMMDD"),2.1);
1 row created.
SQL>
SQL>
SQL> select * from TestTable;
ID   MYNAME     MYDATE      MYNUMBER
---- ---------- --------- ----------
1    Alison     11-JUL-96      12.12
2    Jason      22-JUN-97     -12.12
3    Smith      13-MAY-98       22.1
4    Tailor     24-JUN-99      -2.12
5    Darlene    15-APR-00        2.1
SQL>
SQL>
SQL>
SQL> -- MOD: Returns the remainder of n/m where both n and m are integers.
SQL>
SQL> select MyNumber, MOD(MyNumber, 3) from TestTable;
  MYNUMBER MOD(MYNUMBER,3)
---------- ---------------
     12.12             .12
    -12.12            -.12
      22.1             1.1
     -2.12           -2.12
       2.1             2.1
SQL>
SQL>
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>

MOD() returns the remainder of <numeric_expression1> divided by <numeric_expression2>

  
SQL>
Syntax: MOD(<numeric_expression1>,<numeric_expression2>)
SQL>
SQL>
SQL> SELECT
  2     MOD(10,5) remainder1,
  3     MOD(10,3) remainder2 from dual;
REMAINDER1 REMAINDER2
---------- ----------
         0          1

MOD(x, y):Returns the remainder when x is divided by y

 

SQL>
SQL> --MOD(x, y):Returns the remainder when x is divided by y.
SQL>
SQL> select MOD(8, 3) from dual;
  MOD(8,3)
----------
         2
SQL>

select mod( 6 )

  
SQL>
SQL> select mod( 150.12, 2.6 )
  2  from dual;
MOD(150.12,2.6)
---------------
           1.92
1 row selected.
SQL>
SQL> --

Use mod() function to get all event employee id

 
SQL>
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "Coder", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "Coder", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "Coder", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL>
SQL> select empno as odd_empno
  2  ,      ename
  3  from   emp
  4  where  mod(empno,2) = 1;
 ODD_EMPNO ENAME
---------- --------
         1 Tom
         3 Wil
         5 Mary
         7 Chris
         9 Peter
        11 Ana
        13 Fake
7 rows selected.
SQL>
SQL> drop table emp;
Table dropped.