SQL Functions: A function is a special type of command word in the SQL99 command set. In effect, functions are one-word commands that return a single value.

The value of a function can be determined by input parameters, as with a function that averages a list of database values. But many functions do not use any type of input parameter, such as the function that returns the current system
time, CURRENT_TIME.

Function
Syntax
Example
Result
Working With Character
LOWER
LOWER (expression)
LOWER (‘SQL Course’)
sql course
UPPER
UPPER (expression)
UPPER (‘SQL Course’)
SQL COURSE
INITCAP
INITCAP (expression)
INITCAP (‘SQL course’)
Sql Course
CONCAT
CONCAT (expression1, expression2)
CONCAT (‘Hello’, ‘World’)
HelloWorld
SUBSTR
SUBSTR (expression, m[,n])
SUBSTR (‘HelloWorld’,1,5)
Hello
LENGTH
LENGTH (expression)
LENGTH (‘HelloWorld’)
10
INSTR
INSTR (expression, ‘string’,[,m],[n])
INSTR (‘Bangladesh’, ‘a’, 1, 2)
6
LPAD
LPAD (expression, n, ‘string’)
LPAD (salary, 10, ‘*’)
*****24000
RPAD
RPAD (expression, n, ‘string’)
RPAD (salary, 10, ‘*’)
24000*****
TRIM
TRIM (leading | both, trim_character from trim_source)
TRIM (‘H’, from ‘HelloworldHHH’)
elloworld
REPLACE
REPLACE (text, search_string, replacement_string)
REPLACE (‘JACK and JUE’, ‘J’, ‘BL’)
BLACK and BLUE
Working With Number
ROUND
ROUND (expression, n)
ROUND (45.926, 2)
45.93
TRUNC
TRUNC (expression, n)
TRUNC (45.926, 2)
45.92
MOD
MOD (m, n)
MOD (1600, 300)
100
MONTHS_BETWEEN
MONTHS_BETWEEN (date1, date2)
MONTHS_BETWEEN (’13-Sep-13’,’12-Sep-1995’)
216.064007
Working With Date & Time
ADD_MONTHS
ADD_MONTHS (date, n)
ADD_MONTHS (’11-JAN-95’, 6)
11-JUL-95
NEXT_DAY
NEXT_DAY (date, ‘char’)
NEXT_DAY (’01-SEP-95’, ‘FRIDAY’)
08-SEP-95
LAST_DAY
LAST_DAY (date)
LAST_DAY (’01-FEB-95’)
28-FEB-95
ROUND
ROUND (date[, ‘fmt’])
ROUND (SYSDATE, ‘MONTH’)
01-SEP-13
TRUNC
TRUNC (date[, ‘fmt’])
TRUNC (SYSDATE, ‘YEAR’)
01-JAN-13