Functions : Functions are pre-defined programs which returns always one value

Types of Functions: 1. Single Row Functions 2. Group Functions

Single Row Functions : The function which acts on each and every row.

Types of single row functions

1. Characters / String Functions
2. Numbers Functions
3. Date functions
4. Conversion Functions

Character Functions

SQL> select ‘software’||’hardware’ from dual;

‘SOFTWARE’||’HAR

softwarehardware

SQL> select length(‘software’) from dual;

LENGTH(‘SOFTWARE’)

             8

SQL> select length(‘software’) from emp_new;

LENGTH(‘SOFTWARE’)

             8
             8
             8
             8
             8
             8

6 rows selected.

SQL> select length(ename) from emp_new;

LENGTH(ENAME)

        6
        5
        3


        6
        7
        6

6 rows selected.

SQL> select ename,length(ename) from emp_new;

ENAME LENGTH(ENAME)

---

CHANDU 6
kumar 5
RAM 3
RAMESH 6
KRISHNA 7
SHEKAR 6

6 rows selected.

SQL> select upper(‘software’) from dual;

UPPER(‘S

SOFTWARE

SQL> select upper(ename) from emp_new;

UPPER(ENAM

CHANDU
KUMAR
RAM
RAMESH
KRISHNA
SHEKAR

6 rows selected.

SQL> select lower(ename) from emp_new;

LOWER(ENAM

chandu
kumar
ram
ramesh
krishnas
shekar

6 rows selected.

SQL> select initcap(‘software’) from dual;

INITCAP(

Software

SQL> select substr(‘software’,5,4) from dual;

SUBS

ware

SQL> select substr(‘nagarjunasagar’,4,5) from dual;

SUBST

arjun

SQL> select substr(‘nagarjunasagar’,4,-2) from dual;

S

SQL> select instr(‘software’,’f’) from dual;

INSTR(‘SOFTWARE’,’F’)

                3

SQL> select lpad(‘software’,15) from dual;

LPAD(‘SOFTWARE’

   software

SQL> select lpad(‘software’,15,’#’) from dual;

LPAD(‘SOFTWARE’

#software

SQL> select lpad(‘software’,8) from dual;

LPAD(‘SO

software

SQL> select lpad(‘software’,5) from dual;

LPAD(

softw

SQL> select rpad(‘software’,15,’#’) from dual;

RPAD(‘SOFTWARE’

software#######

SQL> select replace(‘software’,’soft’,’hard’) from dual;

REPLACE(

hardware

SQL>
SQL> select concat(‘soft’,’ware’) from dual;

CONCAT(‘

software

SQL> select trim(‘ software ‘) from dual;

TRIM(‘SO

software

SQL> select ‘soft’||’ ware’ from dual;

‘SOFT’||’WA

soft ware

SQL> select ‘soft’||ltrim(‘ ware’) from dual;

‘SOFT’||

software

SQL>
SQL> select ‘soft ‘||’ware’ from dual;

‘SOFT’||’WAR

soft ware

SQL> select rtrim(‘soft ‘)||’ware’ from dual;

RTRIM(‘S

software

SQL>
SQL> select lpad(‘software’,3) from dual;

LPA

sof

SQL>

Share this post: on Twitter on Facebook on Google+