Functions in Oracle DBMS


The different types of functions in Oracle are −

  • Oracle String Functions
  • Oracle Numeric Functions
  • Oracle Date Functions

More details about these functions are given as follows −

Oracle String Functions

The following are the Oracle String Functions −

ASCII(str)

This function returns the ASCII or numeric value of the first word in the string str provided. If it is an empty string, it returns 0. For example:

SQL> SELECT ASCII('Apple');
+---------------------------------------------------------+
| ASCII('Apple') |
+---------------------------------------------------------+
| 65 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

This returns the ASCII value of A i.e. 65 as it is the first character in the string.

CONCAT(str1,str2…..strn)

This function returns the string that forms by concatenating all the strings in the argument list.These strings may be only two or multiple but they will all be concatenated. For Example:

SQL> SELECT CONCAT('Sky', 'Is', 'Beautiful');
+---------------------------------------------------------+
| CONCAT('Sky', 'Is', 'Beautiful') |
+---------------------------------------------------------+
|SkyIsBeautiful |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Three strings 'Sky', 'Is', 'Beautiful' are concatenated into a single string i.e ‘SkyIsBeautiful’

LENGTH(str)

This function returns the length of the string str in bytes.For Example:

SQL> SELECT LENGTH('happy');
+---------------------------------------------------------+
| LENGTH('happy') |
+---------------------------------------------------------+
| 5 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The length of the string “happy” is returned in bytes i.e 5.

LOWER(str)

All the characters in uppercase are converted to lowercase by this function. For Example:

SQL> SELECT LOWER('BEAUTY');
+---------------------------------------------------------+
| LOWER('BEAUTY') |
+---------------------------------------------------------+
| beauty |
+---------------------------------------------------------+
1 row in set (0.00 sec)

All the characters of “BEAUTY” are converted to lowercase i.e “beauty”

STRCMP(str1,str2)

This function compares both the strings str1 and str2. It returns 0 if both strings are equal, 1 if str1 is greater than str2 and -1 if if str2 is greater than str1. For Example −

SQL> SELECT STRCMP('MIKE', 'MIKE');
+---------------------------------------------------------+
| STRCMP('MIKE', 'MIKE') |
+---------------------------------------------------------+
| 0 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The function returns 0 as the strings “MIKE” and “MIKE” are identical.

UPPER(str)

All the characters in lowercase are converted to uppercase by this function. For Example −

SQL> SELECT UPPER('orange');
+---------------------------------------------------------+
| UPPER('orange') |
+---------------------------------------------------------+
| ORANGE |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The string ”orange” is converted to “ORANGE” in uppercase.

Oracle Numeric Functions

The following are the Oracle Numeric Functions:

ABS(X)

This function returns the absolute value of X. For example:

SELECT ABS(-10);

This returns 10.

MOD(X,Y)

The variable X is divided by Y and their remainder is returned.For example:

SELECT MOD(15,2);

This returns 1.

SIGN(X)

This returns 1 if X is positive, -1 if it is negative and 0 if the value of X is 0. For example:

SELECT SIGN(-20);

This returns -1.

FLOOR(X)

This returns the largest integer value that is either less than X or equal to it. For example:

SELECT FLOOR(8.3);

This returns 8.

CEIL(X)

This returns the smallest integer value that is either more than X or equal to it. For example −

SELECT CEIL(8.3);

This returns 9.

POWER(X,Y)

This function returns the value of X raised to the power of Y. For example:

SELECT POWER(3,2);

This returns 9.

Oracle Date Functions

The following are the Oracle String Functions:

SYSDATE()

This function returns the current date and time of the system. It is one of the most popular oracle functions. SYSDATE() is popularly used with the function TO_CHAR(). For example:

TO_CHAR(SYSDATE, ‘MM-DD-YYYY HH:MM:SS’);

This returns the system date and time in the form of a string. In this case, it will be ‘ 08-01-2018 12:28:34’.

MONTHS_BETWEEN(x,y)

This function takes two values namely x and y which are in the form of months. It returns the number of months between x and y.

For example: The number of months an employee has worked at the company can be found by using MONTHS_BETWEEN() with arguments SYSDATE and the date the employee joined.

SELECT MONTHS_BETWEEN (SYSDATE, EMP_JOIN_DATE)
FROM EMP;

Consider the Employee joining date as 1-January-2018 and the system date as 1-August-2018. Therefore the above returns 7.

ADD_MONTHS(d,n)

This function gives the same day as d, n number of months away. The value of n can be positive or negative.For example:

SELECT SYSDATE, ADD_MONTHS (SYSDATE,2)
FROM DUAL;

This function will return the sysdate and the date 2 months after the sysdate i.e. ‘1-August-2018’ and ‘1-October-2018’.

LAST_DAY(d)

This function returns the last day of the month for the specific month d provided in the function. For example:

SELECT SYSDATE, LAST_DAY (SYSDATE)
FROM DUAL;

This returns the system date and the last day of the particular month for the system date i.e. ‘1-August-2018’ and ‘31-August-2018’.

Updated on: 20-Jun-2020

5K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements