Oracle SQL Functions

SQL Functions

SQL functions are built into Oracle and are available for use in various appropriate SQL statements. You can also create your own function using PL/SQL.

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.

Number Functions

Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits.

The number functions available in Oracle are:

ABS  ACOS ASIN ATAN ATAN2 BITAND CEIL COS COSH EXP FLOOR LN LOG
MOD POWER ROUND (number) SIGN SIN SINH SQRT TAN TANH TRUNC (number)
 

ABS

ABS returns the absolute value of n.

The following example returns the absolute value of -87:

SELECT ABS(-87) "Absolute" FROM DUAL;

 Absolute

----------

        87

ACOS

ACOS returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to pi and are expressed in radians.

The following example returns the arc cosine of .3:

SELECT ACOS(.3)"Arc_Cosine" FROM DUAL;

Arc_Cosine

----------

1.26610367

Similar to ACOS, you have ASIN (Arc Sine), ATAN (Arc Tangent) functions.

CIEL

Example:

select ciel(3.456) “Ciel” from dual;

Ciel

---------

        4

FLOOR         

Returns the highest integer below the given number.

Example:

The following function return the highest integer below 3.456;

select floor(3.456) “Floor” from dual;

Floor

------------

        3

COS

Returns the cosine of an angle (in radians).

Example:

The following example returns the COSINE angle of 60 radians.

select  cos(60) “Cosine” from dual;

SIN

Returns the Sine of an angle (in radians).

Example:

The following example returns the SINE angle of 60 radians.

select  SIN(60) “Sine” from dual;

TAN

Returns the Tangent of an angle (in radians).

Example:

The following example returns the tangent angle of 60 radians.

select  Tan(60) “Tangent” from dual;

Similar to SIN, COS, TAN  functions hyperbolic functions  SINH, COSH, TANH are also available in oracle.

MOD

Returns the remainder after dividing m with n.

Example

The following example returns the remainder after dividing 30 by 4.

Select mod(30,4) “MOD” from dual;

MOD

---------

        2

POWER

Returns the power of m, raised to n.

Example

The following example returns the 2 raised to the power of 3.

select  power(2,3) “Power” from dual;

POWER

---------

        8

EXP

Returns the e raised to the power of n.

Example

The following example returns the e raised to power of 2.

select exp(2) “e raised to 2” from dual;

E RAISED TO 2

-------------

LN

Returns natural logarithm of n.

Example

The following example returns the natural logarithm of 2.

select ln(2) from dual;

LN

------------

LOG

Returns the logarithm, base m, of n.

Example

The following example returns the log of 100.

select log(10,100) from dual;

LOG

---------

        2

ROUND

Returns a decimal number rounded of to a given decimal positions.

Example

The following example returns the no. 3.4573 rounded to 2 decimals.

select round(3.4573,2) “Round” from dual;

Round

------------

        3.46

TRUNC

Returns a decimal number Truncated to a given decimal positions.

Example

The following example returns the no. 3.4573 truncated to 2 decimals.

select round(3.4573,2) “Round” from dual;

Round

------------

        3.45

SQRT

Returns  the square root of a given number.

Example

The following example returns the square root of  16.

select  sqrt(16) from dual;

SQRT

---------

        4

Character Functions

Character functions operate on values of dataype  CHAR or VARCHAR.

LOWER

Returns a given string in lower case.

select LOWER(‘SAMI’) from dual;

LOWER

-------------

sami

UPPER

Returns a given string in UPPER case.

select UPPER(‘Sami’) from dual;

UPPER

------------------

SAMI

INITCAP

Returns a given string with Initial letter in capital.

select INITCAP(‘mohammed sami’) from dual;

INITCAP

------------------

Mohammed Sami

LENGTH

Returns the length of a given string.

select length(‘mohammed sami’) from dual;

LENGTH

------------

        13

SUBSTR

Returns a substring from a given string. Starting from position p to n characters.

For example the following query returns “sam” from the string “mohammed sami”.

select substr('mohammed sami',10,3) from dual;

Substr

--------

sam

INSTR

Tests whether a given character occurs in the given string or not. If the character occurs in the string then returns the first position of its occurrence otherwise returns 0.

Example

The following query tests whether the character “a” occurs in string “mohammed sami”

select instr('mohammed sami','a') from dual;

INSTR

--------

4

REPLACE

Replaces a given set of characters in a string with another set of characters.

Example

The following query replaces “mohd” with “mohammed” .

select replace('ali mohd khan','mohd','mohammed') from dual;

REPLACE

---------

ali mohammed khan

TRANSLATE

This function is used to encrypt characters. For example you can use this function to replace characters in a given string with your coded characters.

Example

The following query replaces characters A with B, B with C, C with D, D with E,...Z with A, and a with b,b with c,c with d, d with e ....z with a.

select translate('interface','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz','BCDEFGHIJKLMNOPQRSTUVWXYZAbcdefghijklmnopqrstuvwxyza') “Encrypt” from dual;

Encrypt

-----------

joufsgbdf

SOUNDEX

This function is used to check pronounciation rather than exact characters. For example many people write names as “smith” or “smyth” or “smythe” but they are pronounced as smith only.

Example

The following example compare those names which are spelled differently but are pronouced as “smith”.

Select ename from emp where soundex(ename)=soundex('smith');

ENAME

---------

Smith

Smyth

Smythe

RPAD

Right pads a given string with a given character to n number of characters.

Example

The following query rights pad ename with '*'  until it becomes 10 characters.

select rpad(ename,'*',10) from emp;

Ename

----------

Smith*****

John******

Mohammed**

Sami******

LPAD

Left pads a given string with a given character upto n number of characters.

Example

The following query left pads ename with '*'  until it becomes 10 characters.

select lpad(ename,'*',10) from emp;

Ename

----------

*****Smith

******John

**Mohammed

******Sami

LTRIM

Trims blank spaces from a given string from left.

Example

The following query returns string “       Interface        “ left trimmed.

select ltrim('       Interface       ') from dual;

Ltrim

--------------

Interface 

RTRIM

Trims blank spaces from a given string from Right.

Example

The following query returns string “       Interface        “ right trimmed.

select rtrim('       Interface       ') from dual;

Rtrim

------------

   Interface 

TRIM

Trims a given character from left or right or both from a given string.

Example

The following query removes zero from left and right of a given string.

Select trim(0 from '00003443500') from dual;

Trim

----------

34435

CONCAT

Combines a given string with another string.

Example

The following Query combines ename with literal string “ is a “ and jobid.

Select concat(concat(ename,' is a '),job) from emp;

Concat

----------------

Smith is a clerk

John is a Manager

Sami is a G.Manager

Miscellaneous Single Row Functions

COALESCE

 Coalesce function returns the first not null value in the expression list.

 Example.

 The following query returns salary+commision, if commission is null then returns salary, if salary is also null then returns 1000.

select empno,ename,salary,comm,coalesce(salary+comm,salary,1000) “Net Sal” from emp;

ENAME     SALARY    COMM NET SAL

-----     ------    ---- -------

SMITH     1000      100  1100

SAMI      3000          3000

SCOTT                   1000

RAVI               200  1000

DECODE

            DECODE(expr, searchvalue1, result1,searchvalue2,result2,..., defaultvalue)

Decode functions compares an expr with search value one by one. If the expr does not match any of the search value then returns the default value. If the default value is omitted then returns null.

Example

The following query returns the department names according the deptno. If the deptno does not match any of the search value then returns “Unknown Department”

select decode(deptno,10,'Sales',20,'Accounts,30,'Production,

          40,'R&D','Unknown Dept') As DeptName from emp;

DEPTNAME

----------

Sales

Accounts

Unknown Dept.

Accounts

Production

Sales

R&D

Unknown Dept.

GREATEST

            GREATEST(expr1, expr2, expr3,expr4...)

Returns the greatest expr from a expr list.

Example

select greatest(10,20,50,20,30) from dual;

GREATEST

--------

50

select greatest('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;

GREATEST

--------

TANYA

LEAST

            LEAST(expr1, expr2, expr3,expr4...)

It is simillar to greatest. It returns the least expr from the expression list.

select least(10,20,50,20,30) from dual;

LEAST

--------

10

select least('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;

LEAST

--------

RAVI

NVL

                        NVL2(expr1,expr2)

This function is oftenly used to check null values. It  returns  expr2 if the  expr1 is null, otherwise returns expr1.

Example

The following query returns commission if commission is null then returns 'Not Applicable'.

Select ename,nvl(comm,'Not Applicable') “Comm” from dual;

ENAME     COMM

------    ----

Scott     300

Tiger     450

Sami      Not Applicable

Ravi      300

Tanya     Not Applicable

NVL2

            NVL2(expr1,expr2,expr3)

NVL2 returns  expr2 if expr1 is not null, otherwise return expr3.

Example

The following query returns salary+comm if comm is not null, otherwise just returns salary.

select salary,comm,nvl2(comm,salary+comm,salary) “Income” from emp;

SALARY    COMM      INCOME

------    ----      ------

1000      100       1100

2000               2000

2300      200       2500

3400               3400

NULLIF

            NULLIF(expr1, expr2)

Nullif compares expr1 with expr2. If they are equal then returns null, otherwise return expr1.

Example.

The following query shows old jobs of those employees  who have changed their jobs in the company by comparing the current job with old job in oldemp table.

Select ename,nullif(e.job,o.job) “Old Job” from emp e, oldemp o where e.empno=o.empno;

ENAME         OLD JOB

-----         -------

SMITH         CLERK

SAMI                   

SCOTT         MANAGER

UID

Returns the current session ID of user logged on.

Example

select uid from dual;

UID

----

20

USER

Returns the username of the current user logged on.

select user from dual;

USER

---------

SCOTT

SYS_CONTEXT

SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

EXAMPLE

The following query returns the username of the current user.

Select sys_context('USERENV','SESSION_USER') “Username” from dual;

USERNAME

---------

SCOTT

Similar to SESSION_USER parameter for namespace USERENV the other important parameters are

ISDBA            :To check whether the current user is having DBA privileges or not.

HOST              :Returns the name of  host machine from which the client is connected.

INSTANCE    :The instance identification number of the current instance

IP_ADDRESS: IP address of the machine from which the client is connected.

DB_NAME    :Name of the database as specified in the DB_NAME initialization parameter

VSIZE

                        VSIZE(expr)

Returns the internal representation of expr in bytes.

Example

The following query return the representation of  ename in bytes.

select ename,vsize(ename) as Bytes from emp;

ENAME     BYTES

------    ------

SCOTT     5

SAMI      4

RAVI      4

KIRAN     5

Aggregate Functions

 Aggregate functions return a single value based on groups of rows, rather than single value for each row. You can use Aggregate functions in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.

The important Aggregate functions are :

Avg     Sum     Max     Min      Count     Stddev     Variance

AVG

            AVG( ALL /DISTINCT        expr)

Returns the average value of expr.

Example

The following query returns the average salary of all employees.

select avg(sal) “Average Salary” from emp;

Average Salary

------------------------

2400.40

SUM

                        SUM(ALL/DISTINCT           expr)

Returns the sum value of expr.

Example

The following query returns the sum salary of all employees.

select sum(sal) “Total Salary” from emp;

Total Salary

------------------------

26500

MAX

                        MAX(ALL/DISTINCT          expr)

Returns maximum value of expr.

Example

The following query returns the max salary from the employees.

select max(sal) “Max Salary” from emp;

Maximum Salary

------------------------

4500

MIN

                        MIN(ALL/DISTINCT           expr)

Returns minimum value of expr.

Example

The following query returns the minimum salary from the employees.

select min(sal) “Min Salary” from emp;

Minimum Salary

------------------------

1200

COUNT

                        COUNT(*)      OR      COUNT(ALL/DISTINCT     expr)

Returns the number of rows in the query. If you specify expr then count ignore nulls. If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. COUNT never returns null.

Example

The following query returns the number of  employees.

Select count(*) from emp;

COUNT

------

14

The following query counts the number of employees whose salary is not null.

Select count(sal) from emp;

COUNT

------

12

STDDEV

            STDDEV(ALL/DISTINCT   expr)

STDDEV returns sample standard deviation of expr, a set of numbers.

Example

The following query returns the standard deviation of salaries.

select stddev(sal) from emp;

Stddev

-------  

1430

VARIANCE

            VARIANCE(ALL/DISTINCT          expr)

Variance returns the variance of expr.

Example

The following query returns the variance of salaries.

select variance(sal) from emp;

Variance

-------  

1430