Advanced Oracle SQL: Modulo Functions Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

SELECT
   X.COLUMN_VALUE X,
   Y.COLUMN_VALUE Y,
   MOD(X.COLUMN_VALUE, Y.COLUMN_VALUE) "MOD(X,Y)",
   REMAINDER(X.COLUMN_VALUE, Y.COLUMN_VALUE) "REMAINDER(X,Y)"
FROM
   TABLE(SYS.ODCINUMBERLIST(-30,-20,20,30)) X,
   TABLE(SYS.ODCINUMBERLIST(-7,7)) Y;

         X          Y   MOD(X,Y) REMAINDER(X,Y)
---------- ---------- ---------- --------------
       -30         -7         -2             -2
       -30          7         -2             -2
       -20         -7         -6              1
       -20          7         -6              1
        20         -7          6             -1
        20          7          6             -1
        30         -7          2              2
        30          7          2              2

MOD and REMAINDER return the rest of the integer division. Here 30/7=4 remains 2. MOD truncates the quotient and REMAINDER rounds it. 20/7 is equal to 2.857. For MOD, 20/7=2 remains 6. For REMAINDER, 20/7=3 remains -1. For negative numbers, the sign of the first argument determines the sign of the modulo and the remainder.

SELECT
   MOD(5, 0)
FROM
   DUAL;
  MOD(5,0)
----------
         5

Another special case is modulo 0. In Perl or in C, modulo 0 is illegal. REMAINDER returns an error for modulo 0 but MOD returns the first argument.

Functions to search and modify strings

SELECT
   ENAME,
   SUBSTR(ENAME, 1, 2)
FROM
   EMP;

ENAME      SU
---------- --
SMITH      SM
ALLEN      AL
WARD       WA
JONES      JO
MARTIN     MA
BLAKE      BL
CLARK      CL
SCOTT      SC
KING       KI
TURNER     TU
ADAMS      AD
JAMES      JA
FORD       FO
MILLER     MI

SUBSTR returns a substring of the employee name, starting at position 1. The third parameter is the maximum length; by default, the rest of the string is returned.

SELECT
   INSTR
   (
      'Programming',
      'ra',
      1,
      2
   ) RA
FROM
   DUAL;
        RA
----------
        25

INSTR returns the position of the second match of the string 'ra', starting at position 1.

SUBSTR and INSTR are often used together:

SELECT
   SUBSTR
   (
      COLUMN_VALUE,
      INSTR
      (
         COLUMN_VALUE,
         ' '
      )+1,
      INSTR
      (
         COLUMN_VALUE,
         ' ',
         1,
         2
      )-
      INSTR
      (
         COLUMN_VALUE,
         ' '
      )-1
   ) "WORD2"
FROM
   TABLE(SYS.ODCIVARCHAR2LIST('Programming'));

WORD2
----------
Oracle

The second word is the substring starting right after the first space and for a length equal to the difference between the position of the first space and the position of the second space.

LENGTH returns the length of a string. LPAD and RPAD are left and right padding functions.

SELECT
   LPAD(DNAME, 20, '.') LEFT,
   RPAD(DNAME, 20, '.') RIGHT,
   RPAD(LPAD(DNAME, 10+LENGTH(DNAME)/2, '.'), 20, '.') MIDDLE
FROM
   DEPT;

LEFT                 RIGHT                MIDDLE
-------------------- -------------------- --------------------
..........ACCOUNTING ACCOUNTING.......... .....ACCOUNTING.....
............RESEARCH RESEARCH............ ......RESEARCH......
...............SALES SALES............... .......SALES........
..........OPERATIONS OPERATIONS.......... .....OPERATIONS.....

By default, the padding character is a space. LPAD adds the character to the left and RPAD to the right. To get the center effect, the string is first padded to 10 + half of the length of the string, then padded to 20.

To remove characters from the left and from the right, three functions are available - TRIM, LTRIM and RTRIM. By default, TRIM removes trailing and leading spaces.

SELECT
   ENAME,
   TRIM(LEADING 'S' FROM ENAME),
   TRIM(TRAILING 'S' FROM ENAME),
   TRIM(BOTH 'S' FROM ENAME)
FROM
   EMP
WHERE
   ENAME LIKE '%S%';

ENAME      TRIM(LEADI TRIM(TRAIL TRIM(BOTH'
---------- ---------- ---------- ----------
SMITH      MITH       SMITH      MITH
JONES      JONES      JONE       JONE
SCOTT      COTT       SCOTT      COTT
ADAMS      ADAMS      ADAM       ADAM
JAMES      JAMES      JAME       JAME

TRIM removes either spaces, by default, or any other single character from the string by taking them from either the left, the right or from both sides (default). The leading and trailing Ss are removed from the employee names:

SELECT
   ENAME,
   LTRIM(ENAME, 'BCDFGHJKLMNPQRSTVWXZ'),
   RTRIM(ENAME, 'BCDFGHJKLMNPQRSTVWXZ')
FROM
   EMP
WHERE
   ROWNUM<6;

ENAME      LTRIM(ENAM RTRIM(ENAM
---------- ---------- ----------
SMITH      ITH        SMI
ALLEN      ALLEN      ALLE
WARD       ARD        WA
JONES      ONES       JONE
MARTIN     ARTIN      MARTI

LTRIM and RTRIM remove either spaces, by default, or any other character from a character string from the left or from the right. The consonants left and right to the name are trimmed. Note that TRIM does not support the removal of more than one character while LTRIM and RTRIM support removing character from a list of character, i.e. a string:

SELECT
   *
FROM
   TABLE(SYS.ODCIVARCHAR2LIST('123','ABC456','789GHI','JKL'))
WHERE
   LTRIM(COLUMN_VALUE,'0123456789') IS NULL;

COL
---
123

The LTRIM functions returns an empty string where the string contains only digits.

TRANSLATE substitutes one character for another character and REPLACE substitutes a string for another string:

SELECT
   LOC,
   REPLACE(LOC,'YORK','ORLEANS'),
   TRANSLATE(LOC,'AOIEY ','@013')
FROM
   DEPT;

LOC           REPLACE(LOC,'YO TRANSLATE(LOC
------------- --------------- -------------
NEW YORK      NEW ORLEANS     N3W0RK
DALLAS        DALLAS          D@LL@S
CHICAGO       CHICAGO         CH1C@G0
BOSTON        BOSTON          B0ST0N

The string 'YORK' is searched and replaced by the string 'ORLEANS'. When the last argument is omitted, the searched string is deleted from the original string.

TRANSLATE substitutes each 'A', 'O', 'E', 'I', 'Y' and ' ' with '@', '0', '3',  '1', NULL and NULL, respectively. When the third argument is shorter than the second, the characters from the first string that have no correspondence are removed. The third argument is not optional and if it is NULL, TRANSLATE returns NULL.

UPPER, LOWER and INITCAP change the case of the string:

SELECT
   UPPER(COLUMN_VALUE),
   LOWER(COLUMN_VALUE),
   INITCAP(COLUMN_VALUE)
FROM
   TABLE(SYS.ODCIVARCHAR2LIST('JoHn sMiTh'));

UPPER(COLU LOWER(COLU INITCAP(CO
---------- ---------- ----------
JOHN SMITH john smith John Smith

UPPER returns everything in uppercase, LOWER in lowercase and INITCAP capitalizes the first letter of each word and sets the other letters to lowercase.