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
|