Contact Us

Oracle Scalar Functions

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

 

 

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…