//
you're reading...
SQL

A Short Guide to Oracle SQL [VI] – Scalar Functions and Regular Exp.

Oracle provides a wide range of built-in functions. These functions increased our ability to manipulate the information we retrieve using basic commands of SQL. A function, in general sense, can be seen as a block of code that accepts an arguments (sometimes not) and always return a output (a value).

Oracle database offers wide rang of function category like Scalar, Aggregate, Analytic, Model, user-defined etc. But this post basically deal with generally used Single-row (scalar) functions.

Scalar function operates on a single row at a time and return a single row of result for every row processed while a Group functions operates on a group of rows at a time and gives one row of output per group.

SINGLE ROW FUNCTIONS

Single row functions further divided into different categories starting with character functions, numeric functions, NULL related functions to hierarchical functions, XML functions etc. Here we talk about Character functions (incl. regular expressions), Numeric functions, Date functions, Conversion Functions, NULL related functions and some generally used function (like CASE, DECODE etc.). Details about other functions can be find in Oracle SQL Language Reference document.

NOTE:
Scalar functions can appear in SELECT lists, WHERE, ORDER BY and HAVING clauses.

Character Functions

Character functions accepts a character input (a table’s column or an expression) and returns a results in the forms of character or number.

CHR
CHR(n) returns the character having the ASCII value of n.

-- CHR(35), CHR(32) and CHR(10) returns a hash mark(#), a space and a new line character respectively.

hr@O11R2> SELECT CHR(35)||CHR(32)||CHR(32)||'Employee ID:'||CHR(32)||employee_id||CHR(10)||
                 CHR(32)||CHR(32)||CHR(32)||'Employee Name:'||CHR(32)||first_name||CHR(32)||last_name||CHR(10)||
                 CHR(32)||CHR(32)||CHR(32)||'Job Name:'||CHR(32)||job_id||CHR(10) "Employee's Details"
          FROM employees
          WHERE department_id=60;

Employee's Details
------------------------------------------------
#  Employee ID: 103
   Employee Name: Alexander Hunold
   Job Name: IT_PROG

#  Employee ID: 104
   Employee Name: Bruce Ernst
   Job Name: IT_PROG

#  Employee ID: 105
   Employee Name: David Austin
   Job Name: IT_PROG

#  Employee ID: 106
   Employee Name: Valli Pataballa
   Job Name: IT_PROG

#  Employee ID: 107
   Employee Name: Diana Lorentz
   Job Name: IT_PROG

ASCII
Returns the ASCII(char) value of given character.

hr@O11R2> SELECT ASCII('A'), ASCII('S'), ASCII('C'), ASCII('I'), ASCII('I')
       FROM dual;

ASCII('A') ASCII('S') ASCII('C') ASCII('I') ASCII('I')
---------- ---------- ---------- ---------- ----------
        65         83         67         73         73

CONCAT
CONCAT(char1, char2) merges data/value of the two fields. The difference between Concatenation operator (||) and CONCAT is, latter can combined only two columns or string literals.

-- Single row function can be nested to any level of depth.

hr@O11R2> SELECT  CONCAT(first_name, last_name) "Employee Name V.1", CONCAT(first_name,CONCAT(' ', last_name)) "Employee Name V.2"
         FROM employees
         WHERE department_id=60;

Employee Name V.1                             Employee Name V.2
--------------------------------------------- ----------------------------------------------
AlexanderHunold                               Alexander Hunold
BruceErnst                                    Bruce Ernst
DavidAustin                                   David Austin
ValliPataballa                                Valli Pataballa
DianaLorentz                                  Diana Lorentz

INITCAP
INITCAP(char) converts first letter of each word in upper case and remaining letters to lower case.

hr@O11R2> SELECT job_id "JOB ID", INITCAP(job_id) "JOB ID"
        FROM employees
        WHERE department_id=20;

JOB ID     JOB ID
---------- ----------
MK_MAN     Mk_Man
MK_REP     Mk_Rep

LOWER
LOWER(char) converts character strings to lower case.

hr@O11R2> SELECT job_id "JOB ID", LOWER(job_id) "JOB ID"
        FROM employees
        WHERE department_id=20;

JOB ID     JOB ID
---------- ----------
MK_MAN     mk_man
MK_REP     mk_rep

UPPER
UPPER(char) converts character strings to upper case.

hr@O11R2> SELECT first_name, UPPER(first_name)
        FROM employees
        WHERE department_id=30;

FIRST_NAME           UPPER(FIRST_NAME)
-------------------- --------------------
Den                  DEN
Alexander            ALEXANDER
Shelli               SHELLI
Sigal                SIGAL
Guy                  GUY
Karen                KAREN

LPAD(expr1,n[,expr2]) is used to fill the left area of character strings(expr1) with specified character/character string (expr1) to specified length n. expr2 is optional and defaults to a blank.

hr@O11R2> SELECT salary, LPAD(salary, 7, '0') "Padded Salary"
       FROM employees
       WHERE department_id=30;

    SALARY Padded
---------- -------
     11000 0011000
      3100 0003100
      2900 0002900
      2800 0002800
      2600 0002600
      2500 0002500

RPAD
RPAD(expr1,n[,expr2]) is used to right padded expr1 with expr2 to length n. expr2 is optional and defaults to a blank.

hr@O11R2> SELECT RPAD(first_name||' '||last_name,40,'.')||salary "Emp. salary for dept. 100"
       FROM employees
       WHERE department_id=100;

Emp. salary for dept. 100
-----------------------------------------------------------
Nancy Greenberg.........................12008
Daniel Faviet...........................9000
John Chen...............................8200
Ismael Sciarra..........................7700
Jose Manuel Urman.......................7800
Luis Popp...............................6900

6 rows selected.

TRIM
TRIM([[expr1],expr2 FROM], expr3) used to trim leading or trailing or characters (or both) from a character string. expr1 can be a keyword LEADING, TRAILING or BOTH (defaults to BOTH). expr2 and expr3 are character string. Default for expr2 is a space.

hr@O11R2> SELECT employee_id, salary,  trim(trailing 0 from salary) "Trimmed Salary", hire_date, 
                 trim(leading 0 from hire_date) "Trimmed Hire Date"
        FROM employees
        WHERE department_id=60;

EMPLOYEE_ID     SALARY Trimmed Salary                           HIRE_DATE Trimmed H
----------- ---------- ---------------------------------------- --------- ---------
        103       9000 9                                        03-JAN-06 3-JAN-06
        104       6000 6                                        21-MAY-07 21-MAY-07
        105       4800 48                                       25-JUN-05 25-JUN-05
        106       4800 48                                       05-FEB-06 5-FEB-06
        107       4200 42                                       07-FEB-07 7-FEB-07

LTRIM
LTRIM(char [,set]) removes character from the left of char until the first character not in the set. set is optional and defaults to a blank.

hr@O11R2> SELECT 'HELLO WORLD' Example , LTRIM('******HELLO WORLD******', '*') "LTRIM Example"
      FROM dual;

EXAMPLE     LTRIM Example
----------- -----------------
HELLO WORLD HELLO WORLD******

RTRIM
RTRIM(char [,set]) removes character from the right of char until the first character not in the set. set is optional and defaults to a blank.

hr@O11R2> SELECT 'HELLO WORLD' Example , RTRIM('******HELLO WORLD******', '*') "RTRIM Example"
      FROM dual;

EXAMPLE     RTRIM Example
----------- -----------------
HELLO WORLD ******HELLO WORLD

SUBSTR
SUBSTR(char, p [, l ]) returns l character long sub-string ofchar, beginning at position p. l is optional and if omitted, returns to the end of char. p can be positive, negative or 0. If negative, position is counted backward from the end of char.

hr@O11R2> SELECT phone_number, substr(phone_number,9) "Ext.", substr(phone_number,-4,4) "Ext." 
      FROM employees
      WHERE department_id=100;

PHONE_NUMBER         Ext.         Ext.
-------------------- ------------ ----
515.124.4569         4569         4569
515.124.4169         4169         4169
515.124.4269         4269         4269
515.124.4369         4369         4369
515.124.4469         4469         4469
515.124.4567         4567         4567

6 rows selected.

LENGTH
LENGTH(char) returns the length of char.

hr@O11R2> SELECT ename, LENGTH(ename), salary, email, LENGTH(email)
     FROM emp;

ENAME                                          LENGTH(ENAME)     SALARY EMAIL                     LENGTH(EMAIL)
---------------------------------------------- ------------- ---------- ------------------------- -------------
Jennifer Whalen                                           15       4400 JWHALEN                           25
Michael Hartstein                                         17      13000 MHARTSTE                          25
Pat Fay                                                    7       6000 PFAY                              25
Alexander Hunold                                          16       9000 AHUNOLD                           25
Bruce Ernst                                               11       6000 BERNST                            25
David Austin                                              12       4800 DAUSTIN                           25
Valli Pataballa                                           15       4800 VPATABAL                          25
Diana Lorentz                                             13       4200 DLORENTZ                          25
Steven King                                               11      24000 SKING                             25
Neena Kochhar                                             13      17000 NKOCHHAR                          25
Lex De Haan                                               11      17000 LDEHAAN                           25

11 rows selected.

If char is data type of CHAR then length includes all trailing blanks.

SOUNDEX
SOUNDEX(char) returns string containing the phonetic representation of char. Lets you compare words that sound alike in English but are spelled differently.

hr@O11R2> SELECT e.employee_id, e.first_name, e.last_name, SOUNDEX(e.first_name) 
       FROM employees e, employees p
       WHERE SOUNDEX(e.first_name)=SOUNDEX(p.first_name) AND e.employee_id!=p.employee_id
       ORDER BY 4;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOUN
----------- -------------------- ------------------------- ----
        115 Alexander            Khoo                      A425
        103 Alexander            Hunold                    A425
        196 Alana                Walsh                     A450
        158 Allan                McEwen                    A450
        165 David                Lee                       D130
        105 David                Austin                    D130
        151 David                Bernstein                 D130
        165 David                Lee                       D130
        151 David                Bernstein                 D130
        105 David                Austin                    D130
        114 Den                  Raphaely                  D500
        107 Diana                Lorentz                   D500
        163 Danielle             Greene                    D540
        109 Daniel               Faviet                    D540
        149 Eleni                Zlotkey                   E450
        174 Ellen                Abel                      E450
        177 Jack                 Livingston                J200
        140 Joshua               Patel                     J200
        186 Julia                Dellinger                 J400
        125 Julia                Nayer                     J400
        181 Jean                 Fleaur                    J500
        110 John                 Chen                      J500
        139 John                 Seo                       J500
        181 Jean                 Fleaur                    J500
        145 John                 Russell                   J500
        139 John                 Seo                       J500
        145 John                 Russell                   J500
        110 John                 Chen                      J500
        181 Jean                 Fleaur                    J500
        110 John                 Chen                      J500
        145 John                 Russell                   J500
        139 John                 Seo                       J500
        189 Jennifer             Dilly                     J516
        200 Jennifer             Whalen                    J516
        127 James                Landry                    J520
        131 James                Marlow                    J520
        197 Kevin                Feeney                    K150
        124 Kevin                Mourgos                   K150
        146 Karen                Partners                  K650
        119 Karen                Colmenares                K650
        168 Lisa                 Ozer                      L200
        102 Lex                  De Haan                   L200
        113 Luis                 Popp                      L200
        168 Lisa                 Ozer                      L200
        102 Lex                  De Haan                   L200
        160 Louise               Doran                     L200
        168 Lisa                 Ozer                      L200
        113 Luis                 Popp                      L200
        102 Lex                  De Haan                   L200
        113 Luis                 Popp                      L200
        160 Louise               Doran                     L200
        160 Louise               Doran                     L200
        201 Michael              Hartstein                 M240
        134 Michael              Rogers                    M240
        120 Matthew              Weiss                     M300
        164 Mattea               Marvins                   M300
        150 Peter                Tucker                    P360
        150 Peter                Tucker                    P360
        144 Peter                Vargas                    P360
        152 Peter                Hall                      P360
        152 Peter                Hall                      P360
        144 Peter                Vargas                    P360
        191 Randall              Perkins                   R534
        143 Randall              Matos                     R534
        138 Stephen              Stiles                    S315
        138 Stephen              Stiles                    S315
        128 Steven               Markle                    S315
        100 Steven               King                      S315
        128 Steven               Markle                    S315
        100 Steven               King                      S315
        116 Shelli               Baida                     S400
        205 Shelley              Higgins                   S400
        171 William              Smith                     W450
        206 William              Gietz                     W450

74 rows selected.

INSTR
INSTR (string, substring [, p [ ,n]]) searches string for substring with starting from position p and nth occurrence of char2.
Both position p and occurrence n must be NUMBER data type and if omitted, defaults to 1.

SQL> SELECT s_first_name||' '||s_last_name "Student Name", INSTR(s_first_name||' '||s_last_name, 'a', 4, 2) "INSTR Ex." 
        FROM student;

Student Name                     INSTR Ex.
------------------------------- ----------
Paul Smith                               0
John King                                0
Prakash Nanda                           10
Anil Ray                                 0
Chris Daves                              0
Rucha Sharma                             9
Deepti Dutt                              0
Akshat Verma                            12
Sarah Neilson                            0
Siddhartha Malhotra                     10
Ellie Grace                              0
Yusuf Hussain                            0
John Scott                               0
Clara Liu                                0
Jessica Nicholas                        15
Abhishek Iyer                            0
Donna Baker                              8
Vedant Malik                             9
Sabrina Khan                            11
Rick Irwin                               0

REPLACE
REPLACE (char, search_str [,replace_str]) returns char with every occurrence of search_str is replaced with replacement_str. If replacement_str is left out or NULL then each occurrence of search_str is removed from char.

SQL> SELECT REPLACE('Hello! I''m SQL','SQL','REPLACE') "Replace Ex."
    FROM DUAL;

Replace Ex.
------------------
Hello! I'm REPLACE

TRANSLATE
TRANSLATE(expr, from_string, to_string) returns expr with all occurrence of from_string replaced by the positionally corresponding characters in to_string.

scott@O11R2> SELECT hiredate, TRANSLATE(hiredate, '-', '/') "Join Date" 
        FROM emp;

Hire Date  Join Date
---------  ---------
17-DEC-80  17/DEC/80
20-FEB-81  20/FEB/81
22-FEB-81  22/FEB/81
02-APR-81  02/APR/81
28-SEP-81  28/SEP/81
01-MAY-81  01/MAY/81
09-JUN-81  09/JUN/81
19-APR-87  19/APR/87
17-NOV-81  17/NOV/81
08-SEP-81  08/SEP/81
23-MAY-87  23/MAY/87
03-DEC-81  03/DEC/81
03-DEC-81  03/DEC/81
23-JAN-82  23/JAN/82

Regular Expression

Regular expression used to search and manipulate text data for complex patterns. A pattern is a string of characters that describes the textual data we are trying to match. A Regular expression contains a set of metacharacters to perform for describing complex patterns.You can find Regular Expression Operators and Meta-symbols in Oracle database SQL Reference.

Followings are just the small overview of Regular expression functions (REGEXP_SUBSTR, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_LIKE, REGEXP_COUNT). You can look for details in Oracle Database Advanced Application Developer’s Guide. You can also find this Oracle white paper Introducing Oracle Regular Expressions useful.

REGEXP_REPLACE
REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_param]]]]) searches source_char for the pattern and replaces the matched string with replace_string.

-- 0-9 can be substituted be [:digit:]

hr@O11R2> SELECT phone
       ,REGEXP_REPLACE(phone,
                       '^[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})[^0-9]*([0-9]*)$' ,
                       '(\1) \2-\3') phone_no
       ,REGEXP_REPLACE(phone,
                       '^[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})[^0-9]*([0-9]*)$' ,
                       '\4') ext
       FROM faculty;

PHONE                PHONE_NO             EXT
-------------------- -------------------- -----
2125551234789        (212) 555-1234       789
2125555678229        (212) 555-5678       229
2125551212890        (212) 555-1212       890
2125553434           (212) 555-3434
2125555656           (212) 555-5656
2125557878           (212) 555-7878
2125559090129        (212) 555-9090       129
2125556565           (212) 555-6565

8 rows selected.

Only source_char and pattern are required. All other parameter is optional.

REGEXP_SUBSTR
REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_param [, subexpr ]]]]) searches for the pattern in the source_char and returns the sub-string that matches the specified pattern.

hr@O11R2> SELECT street_address, 
                 REGEXP_SUBSTR(street_address,                -- source string
                               'east|WEST',                   -- pattern
                                3,                            -- starting position         
                                1,                            -- occurrence
                                'i') REGEXP_SUBSTR            -- match parameter (case insensitive)       
       FROM instructor;

STREET_ADDRESS                                     REGEXP_SUBSTR 
-------------------------------------------------- ------------------------------------------------
100 East 87th                                      East
518 West 120th                                     West
210 West 101st                                     West
34 Sixth Ave
34 Maiden Lane
210 West 101st                                     West
254 Bleeker
518 West 120th                                     West
56 10th Avenue
415 West 101st                                     West

10 rows selected.

hr@O11R2> SELECT street_address, 
                 REGEXP_SUBSTR(street_address,                -- source string
                               '(east)|(WEST)',               -- pattern
                                3,                            -- starting position 
                                1,                            -- occurrence
                                'i',                          -- match parameter (case insensitive) 
                                2) REGEXP_SUBSTR              --subexpression to return
       FROM instructor;

STREET_ADDRESS            REGEX
------------------------- -----
100 East 87th
518 West 120th            West
210 West 101st            West
34 Sixth Ave
34 Maiden Lane
210 West 101st            West
254 Bleeker
518 West 120th            West
56 10th Avenue
415 West 101st            West

10 rows selected.

hr@O11R2> SELECT street_address, 
                 REGEXP_SUBSTR(street_address,                -- source string
                               '(east)|(WEST)',               -- pattern
                                3,                            -- starting position 
                                1,                            -- occurrence
                                'i',                          -- match parameter (case insensitive) 
                                1) REGEXP_SUBSTR              --subexpression to return
       FROM instructor;

STREET_ADDRESS            REGEX
------------------------- -----
100 East 87th             East
518 West 120th
210 West 101st
34 Sixth Ave
34 Maiden Lane
210 West 101st
254 Bleeker
518 West 120th
56 10th Avenue
415 West 101st

10 rows selected.

Only source_char and pattern are required. All other parameter is optional.

REGEXP_INSTR
REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_opt [, match_param [, subexpr ]]]]]) searches source_char for the pattern and returns the character position at which the pattern occurs (either the beginning or end of the match depending on the match_param parameter).


hr@O11R2> SELECT street_address, 
                 REGEXP_INSTR(street_address,                -- source string
                              '(east)|(WEst)',               -- pattern
                              3,                             -- starting position 
                              1,                             -- occurrence
                              1,                             -- return option
                              'i',                           -- match parameter (case insensitive)
                              1) REGEXP                      --subexpression to return
       FROM instructor;

STREET_ADDRESS                                         REGEXP
-------------------------------------------------- ----------
100 East 87th                                               9
518 West 120th                                              0
210 West 101st                                              0
34 Sixth Ave                                                0
34 Maiden Lane                                              0
210 West 101st                                              0
254 Bleeker                                                 0
518 West 120th                                              0
56 10th Avenue                                              0
415 West 101st                                              0

10 rows selected.

hr@O11R2> SELECT street_address, 
                 REGEXP_INSTR(street_address,                -- source string
                              '(east)|(WEst)',               -- pattern
                              3,                             -- starting position 
                              1,                             -- occurrence
                              1,                             -- return option
                              'i',                           -- match parameter (case insensitive)
                              2) REGEXP                      --subexpression to return
       FROM instructor;

STREET_ADDRESS                                         REGEXP
-------------------------------------------------- ----------
100 East 87th                                               0
518 West 120th                                              5
210 West 101st                                              5
34 Sixth Ave                                                0
34 Maiden Lane                                              0
210 West 101st                                              5
254 Bleeker                                                 0
518 West 120th                                              5
56 10th Avenue                                              0
415 West 101st                                              5

10 rows selected.

Only source_char and pattern are required. All other parameter is optional.

REGEXP_COUNT
New in Oracle database 11g REGEXP_COUNT (source_char, pattern [, position [, match_param]]) searches source_char for the pattern and returns the number of times the pattern is found in source_char.

hr@O11R2> SELECT street_address, REGEXP_COUNT(street_address,
                                                ' ',
                                                2) REGEXP 
      FROM student;

STREET_ADDRESS                                         REGEXP
-------------------------------------------------- ----------
101-09 120th St.                                            2
7435 Boulevard East #45                                     3
144-61 87th Ave                                             2
320 John St.                                                2
29 Elmwood Ave.                                             2
22-70 41st St.                                              2
Stratton Hall                                               1
38 Bay 26th ST. #2A                                         4
1674 Woodbine St.                                           2
155 Union Ave #211                                          3
501 W Elm St.                                               3
43-44 Kissena Blvd. #155                                    3
268 E. 3rd St                                               3
44-25 59th St.                                              2
3 Salem Rd.                                                 2
51-76 Van Kleeck St.                                        3
322 Atkins Ave.                                             2
1065 Vermont St. 7F.                                        3
11A Emory St.                                               2
109-62 196th St                                             2
509 2nd St #4L                                              3

Only source_char and pattern are required. All other parameter is optional.

REGEXP_LIKE
REGEXP_LIKE(source_char, pattern [, match_param]) performs search for regular expression pattern in source_char and returns a Boolean indicating whether the pattern matched in the given string or not.

hr@O11R2> SELECT city, postal_code 
    FROM locations
    WHERE REGEXP_LIKE(postal_code, '^[A-Z][0-9|A-Z][0-9|A-Z][ |-][0-9][A-Z|0-9][ 0-9|A-Z]$');

CITY                           POSTAL_CODE
------------------------------ ------------
Toronto                        M5V 2L7
Whitehorse                     YSW 9T2
Oxford                         OX9 9ZB

hr@O11R2> SELECT employee_id, first_name||' '||last_name "Employee Name", salary
       FROM employees
       WHERE REGEXP_LIKE(salary,'^[1-2][[:digit:]]{4}');

EMPLOYEE_ID Employee Name                                      SALARY
----------- ---------------------------------------------- ----------
        201 Michael Hartstein                                   13000
        204 Hermann Baer                                        10000
        205 Shelley Higgins                                     12008
        100 Steven King                                         24000
        101 Neena Kochhar                                       17000
        102 Lex De Haan                                         17000
        108 Nancy Greenberg                                     12008
        114 Den Raphaely                                        11000
        145 John Russell                                        14000
        146 Karen Partners                                      13500
        147 Alberto Errazuriz                                   12000
        148 Gerald Cambrault                                    11000
        149 Eleni Zlotkey                                       10500
        150 Peter Tucker                                        10000
        156 Janette King                                        10000
        162 Clara Vishney                                       10500
        168 Lisa Ozer                                           11500
        169 Harrison Bloom                                      10000
        174 Ellen Abel                                          11000

19 rows selected.

Only source_char and pattern are required. All other parameter is optional.

Number Functions

Number functions are designed to manipulated (kind of arithmetic and numeric operations) numeric data and returns numeric value.

ABS
ABS(n) returns the absolute value of n. The parameters ncan be literal numbers, a numeric column from a table, any valid expression or any non-numeric data type that can be implicitly converted into number data type.

SQL> SELECT ABS(-98) "Ex 1", ABS(78.90) "Ex 2", ABS(56.9087600) "Ex 3"
     FROM dual;

      Ex 1       Ex 2       Ex 3
---------- ---------- ----------
        98       78.9   56.90876

CEIL
CEIL(n) returns the smallest integer that is greater than or equal to n.

SQL> SELECT CEIL(23.45) "Ex 1", CEIL(24.89) "Ex 2", CEIL(-67.90) "Ex 3", CEIL(-67.23) "Ex 4"
    FROM dual;

      Ex 1       Ex 2       Ex 3       Ex 4
---------- ---------- ---------- ----------
        24         25        -67        -67

FLOOR
FLOOR(n) returns the largest integer that is less than or equal to n.

hr@O11R2> SELECT FLOOR(23.45) "Ex 1", FLOOR(24.89) "Ex 2", FLOOR(-67.90) "Ex 3", FLOOR(-67.23) "Ex 4"
          FROM dual;

      Ex 1       Ex 2       Ex 3       Ex 4
---------- ---------- ---------- ----------
        23         24        -68        -68

MOD
MOD(n, m) returns the remainder of a division operation (n divided by m).

scott@O11R2> SELECT sal "NUM 1", deptno "NUM 2", MOD(sal,deptno) "MOD Ex"
         FROM emp;

     NUM 1      NUM 2     MOD Ex
---------- ---------- ----------
       800         20          0
      1600         30         10
      1250         30         20
      2975         20         15
      1250         30         20
      2850         30          0
      2450         10          0
      3000         20          0
      5000         10          0
      1500         30          0
      1100         20          0
       950         30         20
      3000         20          0
      1300         10          0

14 rows selected.

POWER
POWER(n, p) returns the result of n raised to the power p.

SQL> SELECT POWER(10,2) "Ex 1", POWER(5,9) "Ex 2", POWER(-7.2,4) "Ex 3"
  FROM DUAL;
      Ex 1       Ex 2       Ex 3
---------- ---------- ----------
       100    1953125  2687.3856

ROUND
ROUND(n [, i]) returns n rounded to i decimal places. If i is positive the n is rounded to two decimal places to the right. If negative, then n rounds to the left side of the decimal position. If i is omitted or 0, then n is rounded to zero decimal places.

scott@O11R2> SELECT ROUND(23.45) "Ex 1", ROUND(24.89) "Ex 2", ROUND(-67.90) "Ex 3", ROUND(-67.23) "Ex 4"
          FROM dual;

      Ex 1       Ex 2       Ex 3       Ex 4
---------- ---------- ---------- ----------
        23         25        -68        -67

TRUNC
TRUNC(n1 [, n2 ]) truncating number n1 to n2 decimal places. If n2 is omitted, n1 is truncated to zero decimal places. If negative, then n1 is truncated to the left side of the decimal position.

scott@O11R2> SELECT TRUNC(23.45) "Ex 1", TRUNC(24.89) "Ex 2", TRUNC(-67.90) "Ex 3", TRUNC(-67.23) "Ex 4"
          FROM dual;
      Ex 1       Ex 2       Ex 3       Ex 4
---------- ---------- ---------- ----------
        23         24        -67        -67

SIGN
SIGN(n) returns the sign of n. That is it returns –1 if n is negative, 1 if n is positive, and 0 if n is 0.

scott@O11R2> SELECT SIGN(23.45) "Ex 1", SIGN(-67.90) "Ex 2",SIGN(0) "Ex 3"
        FROM dual;

      Ex 1       Ex 2       Ex 3
---------- ---------- ----------
         1         -1          0

Date Functions

DATE functions operate on date, timestamp and interval data type. All returns date data type value except MONTHS_BETWEEN.
MONTHS_BETWEEN
MONTHS_BETWEEN(date1, date2) takes two date data type arguments and returns the number of months between two dates.

hr@O11R2> SELECT employee_id, start_date, end_date, MONTHS_BETWEEN(end_date,start_date) "Duration"
      FROM job_history;

EMPLOYEE_ID  START_DAT  END_DATE     Duration
-----------  ---------  ---------  ----------
        102  13-JAN-01  24-JUL-06  66.3548387
        101  21-SEP-97  27-OCT-01  49.1935484
        101  28-OCT-01  15-MAR-05  40.5806452
        201  17-FEB-04  19-DEC-07  46.0645161
        114  24-MAR-06  31-DEC-07  21.2258065
        122  01-JAN-07  31-DEC-07  11.9677419
        200  17-SEP-95  17-JUN-01          69
        176  24-MAR-06  31-DEC-06  9.22580645
        176  01-JAN-07  31-DEC-07  11.9677419
        200  01-JUL-02  31-DEC-06  53.9677419

10 rows selected.

ADD_MONTHS
ADD_MONTHS(date, n) adds the number of months n to specified date. If n is negative, then n is subtracted from date.

hr@O11R2> SELECT ADD_MONTHS('15-JAN-2013',5) "Exam Date" 
       FROM dual;

Exam Date
---------
15-JUN-13

NEXT_DAY
NEXT_DAY(date, char) takes two arguments first is date and second is a string in single quote representing day of week full or abbr.

hr@O11R2> SELECT NEXT_DAY('23-JAN-2013','MON') "Next Mon" 
    FROM dual;

Next Mon
---------
28-JAN-13

LAST_DAY
LAST_DAY(date) returns the date of the last day of the month for specified date.

hr@O11R2> SELECT LAST_DAY('23-JAN-2013') "Last Day" 
    FROM dual;

Last Day
---------
31-JAN-13

hr@O11R2> SELECT LAST_DAY('23-FEB-2013') "Last Day" 
    FROM dual;

Last Day
---------
28-FEB-13

ROUND
ROUND(date [, fmt ]) function returns date rounded to the unit defined by the format model fmt. By default, rounded to the nearest day. A table for format model used with ROUND(date) and TRUNC(date) function can be found in SQL language reference.
If ‘MM’ is used as a format model then date is rounds to the first day in the nearest month.

hr@O11R2> SELECT employee_id, start_date, end_date, MONTHS_BETWEEN(end_date,start_date) "Duration",
              ROUND(MONTHS_BETWEEN(end_date,start_date)) "Duration Modi"
      FROM job_history;

EMPLOYEE_ID START_DAT END_DATE    Duration Duration Modi
----------- --------- --------- ---------- -------------
        102 13-JAN-01 24-JUL-06 66.3548387            66
        101 21-SEP-97 27-OCT-01 49.1935484            49
        101 28-OCT-01 15-MAR-05 40.5806452            41
        201 17-FEB-04 19-DEC-07 46.0645161            46
        114 24-MAR-06 31-DEC-07 21.2258065            21
        122 01-JAN-07 31-DEC-07 11.9677419            12
        200 17-SEP-95 17-JUN-01         69            69
        176 24-MAR-06 31-DEC-06 9.22580645             9
        176 01-JAN-07 31-DEC-07 11.9677419            12
        200 01-JUL-02 31-DEC-06 53.9677419            54

10 rows selected.

TRUNC
TRUNC(date [, fmt ]) function returns date truncated to the unit defined by the format model fmt. By default, truncated to the beginning of the day.

hr@O11R2> SELECT employee_id, start_date, end_date, MONTHS_BETWEEN(end_date,start_date) "Duration",
              TRUNC(MONTHS_BETWEEN(end_date,start_date)) "Duration Modi"
      FROM job_history;

EMPLOYEE_ID START_DAT END_DATE    Duration Duration Modi
----------- --------- --------- ---------- -------------
        102 13-JAN-01 24-JUL-06 66.3548387            66
        101 21-SEP-97 27-OCT-01 49.1935484            49
        101 28-OCT-01 15-MAR-05 40.5806452            40
        201 17-FEB-04 19-DEC-07 46.0645161            46
        114 24-MAR-06 31-DEC-07 21.2258065            21
        122 01-JAN-07 31-DEC-07 11.9677419            11
        200 17-SEP-95 17-JUN-01         69            69
        176 24-MAR-06 31-DEC-06 9.22580645             9
        176 01-JAN-07 31-DEC-07 11.9677419            11
        200 01-JUL-02 31-DEC-06 53.9677419            53

10 rows selected.

CURRENT_DATE
CURRENT_DATE returns current date and time of the user session in the local time zone set.

hr@O11R2> SELECT CURRENT_DATE "Date" 
       FROM DUAL;

Date
---------
23-JAN-13

SYSDATE
SYSDATE returns the current date and time set for the database server’s operating system.

hr@O11R2> SELECT SYSDATE "Date" 
       FROM DUAL;

Date
---------
23-JAN-13

NEW_TIME
NEW_TIME(date, timezone1, timezone2) returns the date in timezone2 for date in timezone1.
The timezone1 and timezone2 constant can be any of these text strings:

    • AST, ADT: Atlantic Standard or Daylight Time
    • BST, BDT: Bering Standard or Daylight Time
    • CST, CDT: Central Standard or Daylight Time
    • EST, EDT: Eastern Standard or Daylight Time
    • GMT: Greenwich Mean Time
    • HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.
    • MST, MDT: Mountain Standard or Daylight Time
    • NST: Newfoundland Standard Time
    • PST, PDT: Pacific Standard or Daylight Time
    • YST, YDT: Yukon Standard or Daylight Time
hr@O11R2> SELECT my_date "GMT", NEW_TIME(my_date,'GMT','PST') "PST"
       FROM clock;

GMT                    PST
--------------------   --------------------
22-JAN-2013 18:30:00   22-JAN-2013 10:30:00

Conversion Functions

Conversion functions used to convert a value of one data type to another data type.
TO_CHAR(number)
TO_CHAR(n [, fmt [, ‘nlsparam’ ] ]) converts the number n to character data type (VARCHAR2) using optional parameter fmt. nlsparm specifies language or location formatting conventions.
nlsparam can have any of the three form.

NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '
scott@O11R2> SELECT empno, ename, TO_CHAR(sal,'$99,999') "In Dollor", 
                    TO_CHAR(sal,'L99,999') "Local Currency", 
                    TO_CHAR(sal,'C99,999','NLS_ISO_CURRENCY=GERMANY') "ISO currency" 
             FROM emp;

     EMPNO ENAME      In Dollo Local Currency    ISO currency
---------- ---------- -------- ----------------- --------------
      7369 SMITH          $800             UK800         EUR800
      7499 ALLEN        $1,600           UK1,600       EUR1,600
      7521 WARD         $1,250           UK1,250       EUR1,250
      7566 JONES        $2,975           UK2,975       EUR2,975
      7654 MARTIN       $1,250           UK1,250       EUR1,250
      7698 BLAKE        $2,850           UK2,850       EUR2,850
      7782 CLARK        $2,450           UK2,450       EUR2,450
      7788 SCOTT        $3,000           UK3,000       EUR3,000
      7839 KING         $5,000           UK5,000       EUR5,000
      7844 TURNER       $1,500           UK1,500       EUR1,500
      7876 ADAMS        $1,100           UK1,100       EUR1,100
      7900 JAMES          $950             UK950         EUR950
      7902 FORD         $3,000           UK3,000       EUR3,000
      7934 MILLER       $1,300           UK1,300       EUR1,300

14 rows selected.

TO_CHAR(date)
TO_CHAR({ datetime | interval } [, fmt [, ‘nlsparam’ ] ]) converts the datetime or interval data type value to character data type (VARCHAR2) using optional parameter fmt. nlsparm specifies language or location formatting conventions and have a value of ‘NLS_DATE_LANGUAGE = language‘.

scott@O11R2> SELECT SYSDATE, TO_CHAR(SYSDATE,'MONTH, DD YYYY TS') "TO_CHAR(dt)" FROM DUAL;

SYSDATE     TO_CHAR(dt)
---------   ------------------------------
23-JAN-13   JANUARY , 23 2013 11:41:10 PM

scott@O11R2> SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') "Today",
                    TO_CHAR(SYSDATE, 'Day:Dd Month yyyy', 'NLS_DATE_LANGUAGE=Dutch') "Heute",
                    TO_CHAR(SYSDATE, 'Day:Dd Month yyyy', 'NLS_DATE_LANGUAGE=spanish') "Hoy",
                    TO_CHAR(SYSDATE, 'Day:Dd Month yyyy', 'NLS_DATE_LANGUAGE=italian') "Oggi"
            FROM DUAL;

Today                     Heute                      Hoy                        Oggi
------------------------- -------------------------- -------------------------- -------------------------
Thursday :24 January 2013 Donnerstag:24 Januar  2013 Jueves   :24 Enero  2013   Giovedì :24 Gennaio 2013

scott@O11R2>

TO_DATE
TO_DATE(char [, fmt [, ‘nlsparam’ ] ]) converts the character data type value to date using optional parameter fmt. nlsparm specifies language or location formatting conventions.

scott@O11R2> SELECT TO_DATE('January 23, 2013', 'MONTH DD, YYYY') "Ex"
     FROM dual;

Ex
---------
23-JAN-13

scott@O11R2> CREATE TABLE clock
     (my_date DATE);

Table created.

scott@O11R2> INSERT INTO clock
        VALUES ('02/02/2013');            
('02/02/2013')
 *
ERROR at line 3:
ORA-01843: not a valid month

scott@O11R2> INSERT INTO clock
       VALUES (TO_DATE('02/02/2013','DD/MM/YYYY'));     

1 row created.

scott@O11R2> SELECT my_date FROM clock;

MY_DATE
---------
02-FEB-13

TO_NUMBER
TO_NUMBER(expr [, fmt [, ‘nlsparam’ ] ]) converts the character data type value to number using optional parameter fmt. nlsparm specifies language or location formatting conventions.

scott@O11R2> SELECT '$12,500' "Ex", TO_NUMBER('$12,500.75','$99,999.99') "Ex 1",
                    TO_NUMBER('$12,500.75','$99G999D99','NLS_NUMERIC_CHARACTERS=''.,'' ') "Ex 2"
         FROM DUAL;

Ex            Ex 1       Ex 2
------- ---------- ----------
$12,500   12500.75   12500.75

NULL-related Functions

NULL represents a unknown value. Any operation or computation performed on NULL value results in another unknown value or gives some unexpected results. It is best to substitute NULL to some actual value like zero, a charater(string) or some number etc.
There are some functions in SQL when performed on NULL returns non-null value.
NVL
NVL(expr1, expr2) lets you convert null value to an actual meaningful value. It takes tow arguments. It returns expr2 if expr1 is NULL. If expr1 is not null, then NVL returns expr1.
expr1 and expr2 can be of any data type as long as oracle is able to convert them implicitly.

scott@O11R2> SELECT empno, ename, job, sal, NVL(TO_CHAR(comm),'N/A') "Comm", sal+NVL(comm,0) "Gross Sal"
          FROM emp;

     EMPNO ENAME      JOB              SAL Comm                                      Gross Sal
---------- ---------- --------- ---------- ---------------------------------------- ----------
      7369 SMITH      CLERK            800 N/A                                             800
      7499 ALLEN      SALESMAN        1600 300                                            1900
      7521 WARD       SALESMAN        1250 500                                            1750
      7566 JONES      MANAGER         2975 N/A                                            2975
      7654 MARTIN     SALESMAN        1250 1400                                           2650
      7698 BLAKE      MANAGER         2850 N/A                                            2850
      7782 CLARK      MANAGER         2450 N/A                                            2450
      7788 SCOTT      ANALYST         3000 N/A                                            3000
      7839 KING       PRESIDENT       5000 N/A                                            5000
      7844 TURNER     SALESMAN        1500 0                                              1500
      7876 ADAMS      CLERK           1100 N/A                                            1100
      7900 JAMES      CLERK            950 N/A                                             950
      7902 FORD       ANALYST         3000 N/A                                            3000
      7934 MILLER     CLERK           1300 N/A                                            1300

14 rows selected.

NVL2
A variation of NVL, NVL2(expr1, expr2, expr3) returns value for both condition null and not null. It takes three parameter and return expr2 if expr1 is not null and returns expr3 if expr1 is null. Parameter expr2 and expr3 can have any data type except LONG but must be compatible

scott@O11R2> SELECT empno, ename, job, sal, NVL(TO_CHAR(comm),'N/A') "Comm", NVL2(comm,sal+comm,sal) "Gross Sal"
     FROM emp;

     EMPNO ENAME      JOB              SAL Comm                                      Gross Sal
---------- ---------- --------- ---------- ---------------------------------------- ----------
      7369 SMITH      CLERK            800 N/A                                             800
      7499 ALLEN      SALESMAN        1600 300                                            1900
      7521 WARD       SALESMAN        1250 500                                            1750
      7566 JONES      MANAGER         2975 N/A                                            2975
      7654 MARTIN     SALESMAN        1250 1400                                           2650
      7698 BLAKE      MANAGER         2850 N/A                                            2850
      7782 CLARK      MANAGER         2450 N/A                                            2450
      7788 SCOTT      ANALYST         3000 N/A                                            3000
      7839 KING       PRESIDENT       5000 N/A                                            5000
      7844 TURNER     SALESMAN        1500 0                                              1500
      7876 ADAMS      CLERK           1100 N/A                                            1100
      7900 JAMES      CLERK            950 N/A                                             950
      7902 FORD       ANALYST         3000 N/A                                            3000
      7934 MILLER     CLERK           1300 N/A                                            1300

14 rows selected.

NULLIF
NULLIF(expr1, expr2) is used to compare two expression/value for equality. It takes two parameter and returns NULL if they are equal or the first value if the two values are not equal.

HR@O11R2> SELECT s_first_name "First Name", s_last_name "Last Name", NULLIF(LENGTH(s_first_name),
                 LENGTH(s_last_name)) "Equal Ex"
      FROM student;

First Name      Last Name         Equal Ex
--------------- --------------- ----------
Paul            Smith                    4
John            King
Prakash         Nanda                    7
Anil            Ray                      4
Chris           Daves
Varun           Naidu
Satish          Paul                     6
Rajit           Nair                     5
Keith           Peters                   5
Rucha           Sharma                   5
Deepti          Dutt                     6
Ellie           Grace
Yusuf           Hussain                  5
Steve           Kapoor                   5
Sameer          George
George          Thomson                  6
John            Scott                    4
Clara           Liu                      5
Tanya           Singh
Arvind          Krishna                  6
Emily           Kim                      5
Donna           Baker
...

It is equivalent to the CASE statement:

CASE 
   WHEN value1=value2 
     THEN NULL 
   ELSE 
     value1 
END

COALESCE
COALESCE(expr [, expr ]…) accepts a list of values and returns the first argument that is NOT NULL. It is similar to the NVL function, but it can take multiple alternate values.

scott@O11R2> SELECT empno, ename, sal, comm, sal+coalesce(comm,0) "Gross Salary"
      FROM emp;

     EMPNO ENAME             SAL       COMM Gross Salary
---------- ---------- ---------- ---------- ------------
      7369 SMITH             800                     800
      7499 ALLEN            1600        300         1900
      7521 WARD             1250        500         1750
      7566 JONES            2975                    2975
      7654 MARTIN           1250       1400         2650
      7698 BLAKE            2850                    2850
      7782 CLARK            2450                    2450
      7788 SCOTT            3000                    3000
      7839 KING             5000                    5000
      7844 TURNER           1500          0         1500
      7876 ADAMS            1100                    1100
      7900 JAMES             950                     950
      7902 FORD             3000                    3000
      7934 MILLER           1300                    1300

14 rows selected.

scott@O11R2> SELECT COALESCE(null, null, SUBSTR('coalesce',9), 'COALESCE') "Ex"
       FROM DUAL;

Ex
--------
COALESCE

The equivalent CASE statement for COALESCE When the number of values is 2,

CASE 
   WHEN value1 IS NOT NULL 
     THEN value1 
   ELSE 
     value2 
END

and when the number of values is greater than 2, it is equivalent to:

CASE 
   WHEN value1 IS NOT NULL 
     THEN value1 
   ELSE 
     COALESCE (value2,..., valueN) 
END

Other generally used functions

Followings are some commonly used functions:

DECODE
The DECODE(expr, search, result [, search, result ]… [, default ]) function compares the expr to search. If they are equal it returns result. If no match is found, the DECODE function returns the default value. If no default value is specified, then the default will return a null.

scott@O11R2> SELECT empno, ename, deptno, 
                    DECODE(deptno,10,'NEW YORK', 
                                  20, 'DALLAS', 
                                  30, 'CHICAGO', 
                                  40, 'BOSTON', 
                                  'N/A') "Location"
       FROM emp;

     EMPNO ENAME          DEPTNO Location
---------- ---------- ---------- --------
      7369 SMITH              20 DALLAS
      7499 ALLEN              30 CHICAGO
      7521 WARD               40 BOSTON
      7566 JONES              20 DALLAS
      7654 MARTIN                N/A
      7698 BLAKE              40 BOSTON
      7782 CLARK              10 NEW YORK
      7788 SCOTT              20 DALLAS
      7839 KING               10 NEW YORK
      7844 TURNER             30 CHICAGO
      7876 ADAMS              20 DALLAS
      7900 JAMES              30 CHICAGO
      7902 FORD               20 DALLAS
      7934 MILLER             10 NEW YORK
      8923 Smith              20 DALLAS

15 rows selected.

NOTE:
Oracle SQL Language Reference quotes,
In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.

scott@O11R2> select ename, DECODE(SUBSTR(ename,5,1),null,'NULL',SUBSTR(ename,5,1)) "Ex"
       FROM emp;

ENAME      Ex
---------- ----
SMITH      H
ALLEN      N
WARD       NULL
JONES      S
MARTIN     I
BLAKE      E
CLARK      K
SCOTT      T
KING       NULL
TURNER     E
ADAMS      S
JAMES      S
FORD       NULL
MILLER     E

14 rows selected.


CASE Expression
Like DECODE, CASE also provides a general mechanism for specifying conditional [IF-THEN-ELSE] results.

Simple CASE Expression
A simple CASE expression use expression expr to determine the resulting value. It matches first comparison_expr to expr, if they are equal then it returns the value of the corresponding return_expr.

CASE expr
      WHEN comparison_expr THEN return_expr 
     [{ WHEN comparison_expr THEN return_expr }
     ...
     { WHEN comparison_expr THEN return_expr }]
     [ELSE <def_return_expr> ]
END

If not, then next comparison_expr evaluated against expr (if defined) and it continues until a match is found. If no matches are found, the value of def_return_expr (define in ELSE clause) is returned. If no ELSE clause is defined then Oracle returns null.

scott@O11R2> SELECT empno, ename, deptno,
             CASE deptno
                   WHEN 10 THEN 'NEW YORK'
                   WHEN 20 THEN 'DALLAS'
                   WHEN 30 THEN 'CHICAGO'
                   WHEN 40 THEN 'BOSTON'
                   ELSE 'N/A'
             END "Location"
         FROM emp;

     EMPNO ENAME          DEPTNO Location
---------- ---------- ---------- --------
      7369 SMITH              20 DALLAS
      7499 ALLEN              30 CHICAGO
      7521 WARD               40 BOSTON
      7566 JONES              20 DALLAS
      7654 MARTIN                N/A
      7698 BLAKE              40 BOSTON
      7782 CLARK              10 NEW YORK
      7788 SCOTT              20 DALLAS
      7839 KING               10 NEW YORK
      7844 TURNER             30 CHICAGO
      7876 ADAMS              20 DALLAS
      7900 JAMES              30 CHICAGO
      7902 FORD               20 DALLAS
      7934 MILLER             10 NEW YORK
      8923 Smith              20 DALLAS

15 rows selected.

Searched CASE Expression
The searched CASE Expression allows for test conditions other than equality. It use conditions to determine the returned value. A searched CASE expression evaluates the WHEN condition (in the order we specified) and if the condition is true, then it returns the value of the corresponding return_expr. condition can be any expression comprising expressions, functions etc.

CASE 
      WHEN condition THEN return_expr 
     [{ WHEN condition THEN return_expr }
     ...
     { WHEN condition THEN return_expr }]
     [ELSE <def_return_expr> ]
END

If no condition is found to be true, the value of def_return_expr (define in ELSE clause) is returned. If no ELSE clause is defined then Oracle returns null.

scott@O11R2> SELECT empno, ename, sal,
                    CASE
                         WHEN sal<2400 THEN 'C'
                         WHEN sal<4000 THEN 'B'
                         WHEN sal>4000 THEN 'A'
                    END "Grade"
      FROM d_emp
      ORDER BY "Grade";

     EMPNO ENAME             SAL G
---------- ---------- ---------- -
      7839 KING             6000 A
      8923 Smith            3300 B
      7499 ALLEN            2600 B
      7902 FORD             3300 B
      7844 TURNER           2500 B
      7788 SCOTT            3300 B
      7782 CLARK            3450 B
      7698 BLAKE            3850 B
      7566 JONES            3975 B
      7900 JAMES            1950 C
      7876 ADAMS            2100 C
      7934 MILLER           2300 C
      7654 MARTIN           2250 C
      7369 SMITH            1800 C
      7521 WARD             2250 C

15 rows selected.

NOTE
Oracle does not evaluate the remaining WHEN clauses when the first condition is matched. So the order of the conditional is very important.

GREATEST and LEAST
GREATEST and LEAST returns the largest and smallest of the list of one or more expressions.

GREATEST(expr [, expr ]...)
LEAST(expr [, expr ]...)

All expression after the first are implicitly converted into the data type of first expression before the comparison. If the first expression is any of the character datatypes, a VARCHAR2 is returned and the comparison rules for VARCHAR2 are used for character literal strings.
A NULL in the expression list results in a NULL being returned.

scott@O11R2> SELECT num1, num2, num3, GREATEST(num1,num2,num3) "Largest Val", LEAST(num1,num2,num3) "Smallest Val"
     FROM num_check;

      NUM1       NUM2       NUM3 Largest Val Smallest Val
---------- ---------- ---------- ----------- ------------
     25267      67637      24145       67637        24145
      9067      23567      16376       23567         9067
     11674       3567      16376       16376         3567

scott@O11R2> SELECT GREATEST(1,'23',0) "Ex", LEAST(1,'23',0) "ex" 
       FROM dual;

        Ex         ex
---------- ----------
        23          0

scott@O11R2> SELECT GREATEST(1,'23',NULL) "Ex", LEAST(1,'23',NULL) "ex"
       FROM dual;

        Ex         ex
---------- ----------

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: