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:

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