Types of Functions in SQL

Functions in SQL:

SQL supports functions that used to manipulate data. There are two types of functions in SQL:

• Single Row Functions
• Multiple Row Functions

1. Single Row Functions:

It works with a single row at a time. A single-row function returns a result for every row of the table, on which a query is made. There are three types of single-row SQL functions:

i. Character Functions:

Those are SQL functions that accept a character input and returns character and/or numeric values.

INITCAP (string): These function capitalizes the first character of each word in the string.

LOWER (string): The LOWER function converts all the characters in the string to lowercase letters.

UPPER (string): The UPPER function converts all the characters in the string to uppercase letters.

CONCAT (string1, string2): The CONCAT function returns string1 appended by string2.

SOUNDEX (string): The SOUNDEX function returns a phonetic representation of each word and allows you to compare words that are spelt differently but sound alike.

INSTR(): The INSTR() function is used to find out where the particular pattern of characters occur in the given string.

LENGTH (string): The LENGTH returns the length of its character argument.

LTRIM() and RTRIM(): The LTRIM() and RTRIM() functions take at least one and at most two arguments. The first argument is a character string. The optional second element is either a character or string. These trim functions will remove characters in the first argument string up to this second-argument character.

The second argument is “blank” by default. That is if you provide only one argument, characters up to the blank space are removed.

The RTRIM() function removes characters from the right of the given string.

SUBSTR (string, M, N): This function returns a substring, N character long, from the string, starting from position M. If the number of characters, N is not specified, the string is extracted from position M to end. Note that the blank spaces are also counted.

LPAD(): The LPAD() function is of the following form:
[sql]LPAD(string1, n[, string2])
[/sql]

The first argument is the character string to be operated on. The second is the number of characters to pad it with and the optional third argument is the character to pad it with. The third argument defaults to a blank.

ii. Number Functions:

Those functions accept numeric values and after performing the required operation, return numeric values.

ABS(N): -This function returns the absolute value without any -ve sign of the column or value passed.

CEIL(N): This function is used to find the smallest integer greater than or equal to N.

FLOOR(N): This function is used to find the largest integer less than or equal to N. Note that N can also be a column name.

MOD(M, N): The MOD(M, N) function returns the remainder of M divided by N. If N=0, the function will return M.

POWER(M, N): The POWER function returns M raised to the power N. Note that N must be an integer.

ROUND(): The ROUND() function returns a number rounded as required. It takes two integer arguments. The first argument is the number to be rounded off and the second argument specifies the number of places to which the number should be rounded off.

TRUNC(): These function returns a number with specified number of digits to be truncated.

iii. Date Functions:

Those functions operate on data of the type Date.
SYSDATE: It is used as a column in queries to retrieve the current date and time.

ADD_MONTHS(D, N): This function adds N months to the date D. The result is shown as DATE type.
Here n can be a positive or negative value. If N is positive, the new date will be N months more than the previous value. If it is -N, then the new date will be less than the previous value.

MONTHS_BETWEEN(D1, D2): It returns the number of months between the two dates, D1 and D2. If D1 is later than D2, the result is positive, if D1 is earlier than D2, the result is negative.

TO_CHAR(D, ‘DAY’): It converts the date D to character format. It will give the corresponding name of the weekday.

LAST_DAY(): It is used to return the last date of the given month.

NEXT_DAY(): This function returns the date of a specified day in the next week.

2. Multiple Row Functions:

It works with data of multiple rows at a time and returns aggregated or total values.