This SQL Tutorial provides you a summary of some of the most common Oracle Scalar Functions. For this lesson’s exercises, use this link.
Oracle Scalar Functions allow you to perform different calculations on data values. These functions operate on single rows only and produce one result per row. There are different types of Scalar Functions, this tutorial covers the following:
- String functions – functions that perform operations on character values.
- Numeric functions – functions that perform operations on numeric values.
- Date functions – functions that perform operations on date values.
- Conversion functions – functions that convert column data types.
- NULL-related Functions – functions for handling null values.
Note: this tutorial focuses on Oracle Scalar Functions; for more details about Group Functions, use this link.
Oracle String Functions
|
Syntax |
Description |
Function |
SELECT CONCAT('Hello' , 'World')
FROM dual
-- Result: 'HelloWorld'
|
Returns text strings concatenated |
CONCAT |
SELECT INSTR('hello' , 'e')
FROM dual
-- Result: 2
|
Returns the location of a substring in a string. |
INSTR |
SELECT LENGTH('hello')
FROM dual
-- Result: 5
|
Returns the number of characters of the specified string expression. |
LENGTH |
SELECT RTRIM(' hello ')
FROM dual
-- Result: ' hello'
|
Returns a character string after truncating all trailing blanks. |
RTRIM |
SELECT LTRIM(' hello ')
FROM dual
-- Result: 'hello '
|
Returns a character expression after it removes leading blanks. |
LTRIM |
SELECT REPLACE('hello' , 'e' , '$')
FROM dual
-- Result: 'h$llo'
|
Replaces all occurrences of a specified string value with another string value. |
REPLACE |
SELECT REVERSE('hello')
FROM dual
-- Result: 'olleh'
|
Returns the reverse order of a string value. |
REVERSE |
SELECT SUBSTR('hello' , 2,3)
FROM dual
-- Result: 'ell'
|
Returns part of a text. |
SUBSTR |
SELECT LOWER('HELLO')
FROM dual
-- Result: 'hello'
|
Returns a character expression after converting uppercase character data to lowercase. |
LOWER |
SELECT UPPER('hello')
FROM dual
-- Result: 'HELLO'
|
Returns a character expression with lowercase character data converted to uppercase. |
UPPER |
SELECT INITCAP('hello')
FROM dual
-- Result: 'Hello'
|
Returns a character expression, with the first letter of each word in uppercase, all other letters in lowercase. |
INITCAP |
Oracle Date Functions
| Syntax |
Description |
Function |
SELECT ADD_MONTHS('05-JAN-2001' , 4)
FROM dual
-- Result : '05-MAY-2001'
|
Returns a specified date with additional n months |
ADD_NONTHS |
SELECT EXTRACT (DAY FROM SYSDATE) FROM dual -- Result : 16
|
Returns the value of a specified date. |
EXTRACT |
SELECT LAST_DAY('15-AUG-2014')
FROM DUAL
-- Result: '31-AUG-2014'
|
Returns a date representing the last day of the month for specified date. |
LAST_DAY |
SELECT MONTHS_BETWEEN('01-MAY-2010', '01-JAN-2010')
FROM dual
-- Result : 4
|
Returns the count of months between the specified startdate and enddate |
MONTHS_BETWEEN |
SELECT NEXT_DAY('30-AUG-2014' , 'Sunday')
FROM dual
-- Result: '31-AUG-2014'
|
returns the first weekday that is greater than the specified date. |
NEXT_DAY |
SELECT SYSDATE FROM dual -- Result: (current date)
|
Returns the current database system date. This value is derived from the operating system of the computer on which the instance of Oracle is running. |
SYSDATE() |
Oracle Numeric Functions
|
Syntax |
Description |
Function |
SELECT TRUNC(59.9) FROM dual -- Result: 59 |
Returns an integer that is less than or equal to the specified numeric expression. |
TRUNC |
SELECT CEIL(59.1) FROM dual -- Result: 60 |
Returns an integer that is greater than, or equal to, the specified numeric expression. |
CEIL |
SELECT ROUND(59.9) FROM dual -- Result: 60 SELECT ROUND(59.1) FROM dual -- Result: 59 |
Returns a numeric value, rounded to the specified length or precision. |
ROUND |
Oracle Conversion Functions
|
Syntax |
Description |
Function |
SELECT TO_CHAR(1506) FROM dual -- Result : The string value '98' SELECT TO_CHAR(1507, '$9,999') FROM dual -- Result : The string value '$1,507' SELECT TO_CHAR(sysdate, 'dd/mm/yyyy') FROM dual -- Result : The string value '01/01/2015' |
Converts a date or number to a string |
TO_CHAR |
SELECT TO_DATE('01-MAY-2015')
FROM dual
-- Result : The date value '01-MAY-2015'
SELECT TO_DATE('01/05/2015' , 'dd/mm/yyyy')
FROM dual
-- Result : The date value : '01-MAY-2015'
|
Converts a string value to a date |
TO_DATE |
SELECT TO_NUMBER('9432')
FROM dual
-- Result : The numeric value : 9432
SELECT TO_NUMBER('$9,324' , '$9,999')
-- Result : The numeric value 9324
|
Converts a string value to a number |
TO_NUMBER |
Oracle NULL-Related Functions
|
Syntax |
Description |
Function |
SELECT NVL(NULL,'Somevalue') -- Result: Somevalue |
Replaces NULL with the specified replacement value. |
NVL |
