Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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

Note:  For VARCHAR2 and CHAR, an empty string is null and has a length of NULL. For CLOB, the empty string is not null and has a length of 0.

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. 


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.