Using Single-Row Functions to Customize Output

Single-row functions return a single result row for every row of a queried table or view. For more information, check here.

1. Character Functions:

LOWER, UPPER, INITCAP – those functions are called character case convertion functions. They take only one parameter.  For example:
SQL> select lower('MY NAME IS MRUHTEL') from dual;
LOWER('MYNAMEISMRU
------------------
my name is mruhtel
SQL> select upper('my name is mruhtel') from dual;
UPPER('MYNAMEISMRU
------------------
MY NAME IS MRUHTEL
SQL> select initcap('my name is mruhtel') from dual;
INITCAP('MYNAMEISM
------------------
My Name Is Mruhtel
LENGTH – It returns the length of a string. For example:
SQL> select length('My name is Mruhtel') from dual;
LENGTH('MYNAMEISMRUHTEL')
-------------------------
                       18
SQL> select length(sysdate) from dual;
LENGTH(SYSDATE)
---------------
              9
SUBSTR – it extracts and returns a segment from a given source string. It takes 3 parameters. It has following syntax:
SUBSTR(source string, start position, [number of characters to extract]). If “number of characters to extract” is not specified, it takes as default value number of characters of source string. For example:
SQL> select substr ('my name is mruhtel', 3) from dual;
SUBSTR('MYNAMEIS
----------------
name is mruhtel –> it starts from position 3 and extracts the rest of the string
SQL> select substr ('my name is mruhtel',1) from dual;
SUBSTR('MYNAMEISMR
------------------
my name is mruhtel
SQL> select substr ('my name is mruhtel',3,5) from dual;
SUBST
-----
name –> it starts from position 3 and extracts 5 characters.

INSTR – It locates the position of a string in a source string. It has two mandatory and two optional arguments.
SQL> select instr('my name is mruhtel',3,2) from dual;
INSTR('MYNAMEISMRUHTEL',3,2)
----------------------------
                           0
SQL> select instr('my name is mruhtel',5,2) from dual;
INSTR('MYNAMEISMRUHTEL',5,2)
----------------------------
                           0
SQL> select instr('my name is mruhtel','my') from dual;
INSTR('MYNAMEISMRUHTEL','MY')
-----------------------------
                            1
SQL> select instr('my name is mruhtel','n',3) from dual;
INSTR('MYNAMEISMRUHTEL','N',3)
------------------------------
                             4
SQL> select instr('my name is mruhtel','n') from dual;
INSTR('MYNAMEISMRUHTEL','N')
----------------------------
                           4
SQL> select instr('my name is mruhtel',' ') from dual;
INSTR('MYNAMEISMRUHTEL','')
---------------------------
                          3
SQL> select instr('my name is mruhtel',' ',2,3) from dual
INSTR('MYNAMEISMRUHTEL','',2,3)
-------------------------------
                             11

REPLACE - It replace all occurrences of a search item in a source string with a replacement term. It has following syntax:
REPLACE (source string, search item, [replacement item]).

SQL> select replace ('my name is mruhtel','mruhtel','mira
REPLACE('MYNAME
---------------
my name is mira
SQL> select replace ('my name is mruhtel','mruhtel','john
REPLACE('MYNAME
---------------
my name is john

SQL> select replace ('my name is mruhtel', 'mruhtel') from dual;  ----> if [replacement item] is omitted, each occurrence of a search string will be removed

REPLACE('MY
-----------
my name is

ROUND - It rounds numeric value based on decimal precision scpecified. Its syntax is:
ROUND (source number, decimal precision).
For example:

SQL> select round (16359.69875,1) from dual; -------> if numeric value is greater than 5 we have "round up"occurence

ROUND(16359.69875,1)
--------------------
             16359.7

SQL> select round (16359.643332,3) from dual; ---------> if numeric value is less than 5 we have "round down" occurence

ROUND(16359.643332,3)
---------------------
            16359.643

SQL> select round (16359.643332,-3) from dual; -------> if decimal precision is negative, ti digit significant to the rounding is found n places to the LEFT

ROUND(16359.643332,-3)
----------------------
                 16000

SQL> select round (56987.236, -2) from dual;
ROUND(56987.236,-2)
-------------------
              57000

MONTHS_BETWEEN - computes the number of months between two given data parameters. It has following syntax:
MONTHS_BETWEEN(date1, date2)
For example:
SQL> SELECT MONTHS_BETWEEN('01-JAN-01','01-AUG-01') AS MONTHS_BETWEEN FROM Dual;

MONTHS_BETWEEN
--------------
            -7
SQL> SELECT MONTHS_BETWEEN('21-JAN-01','01-AUG-01') AS MONTHS_BETWEEN FROM Dual;

MONTHS_BETWEEN
--------------
    -6.3548387
LAST_DAY  - it returns the last day of the month in which a particular date falls. It has following syntax:
LAST_DAY(start_date)
 For example:

SQL> select last_day('12-DEC-2009') FROM DUAL;
LAST_DAY(
---------
31-DEC-09

For more details and functions check here