SQL supplies a rich library of in-built functions which can be employed for various tasks.
The essential capabilities of a functions can be the case conversion of strings, in-string or
substring operations, mathematical computations on numeric data, and date operations on
date type values
Single Row functions - Single row functions are the one who work on single row and
return one output per row. For example, length and case conversion functions are
single row functions.
Multiple Row functions - Multiple row functions work upon group of rows and return
one result for the complete set of rows. They are also known as Group Functions.
Single row functions
Single row functions can be character functions, numeric functions, date functions, and
conversion functions. Note that these functions are used to manipulate data items.
These functions require one or more input arguments and operate on each row,
thereby returning one output value for each row. Argument can be a column, literal or
an expression. Single row functions can be used in SELECT statement, WHERE and
ORDER BY clause. Single row functions can be -
Case Conversion functions - Accepts character input and returns a character value.
Functions under the category are UPPER, LOWER and INITCAP.
o UPPER function converts a string to upper case.
o LOWER function converts a string to lower case.
o INITCAP function converts only the initial alphabets of a string to upper case.
Character functions - Accepts character input and returns number or character value.
Functions under the category are CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD,
TRIM and REPLACE.
o CONCAT function concatenates two string values.
o LENGTH function returns the length of the input string.
o SUBSTR function returns a portion of a string from a given start point to an end
point.
o INSTR function returns numeric position of a character or a string in a given string.
o LPAD and RPAD functions pad the given string upto a specific length with a given
character.
o TRIM function trims the string input from the start or end.
o REPLACE function replaces characters from the input string with a given character.
Date functions - Date arithmetic operations return date or numeric values. Functions under
the category are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND
and TRUNC.
o MONTHS_BETWEEN function returns the count of months between the two dates.
o NEXT_DAY function returns the next day of the date specified.
o LAST_DAY function returns last day of the month of the input date.
o ROUND and TRUNC functions are used to round and truncates the date value.
Number functions - Accepts numeric input and returns numeric values. Functions under
the category are ROUND, TRUNC, and MOD.
o ROUND and TRUNC functions are used to round and truncate the number value.
o MOD is used to return the remainder of the division operation between two numbers.