Functions are very powerful feature of SQL which are used to do the followings:

• • Perform calculations on data.
• • Modify individual data item.
• • Manipulate output for groups of rows.
• • Format dates and numbers for display.

Functions are small purpose programs, which may accept argument and return value. Basically there are two types of SQL functions such as :

• • Multiple-row functions: operates on group of rows and returns one result per groups of rows.
• • Single-row functions: Operates on single rows only and returns one result per rows

Let's see some of the properties of single row functions:
Operates on data items:

• • May accept arguments and return one value.
• • ct on each row returned in a query.
• • Returns one row per column.
• • Can modify the data type.
• • Can be nested.

Single row functions are of different types like:

Character functions: input-character	output-character or number
Number functions: input-numeric	output-numeric
Date functions : input-date	output-date or number
Conversion functions: input-any	output-any
Character functions

Lower(column| expression)- converts character values to lower case.

Example:

The above example will display the names of the employee in the employee table in lower case.

Upper(column|expression):- converts character values to uppercase.

Example:

The above example will display the names of employee in the employee table in uppercase.

Initcap(column|expression):- converts the first character of each word to uppercase and the rest of the character to lowercase.

Example:

The above example will display the names of the employee in the employee table in Title case.

Concate(column1|expression1, column2|expression2):- joins values together. It can take two arguments at a time.

Example:

The above example will display all the employee names in the employee table preceded by Mr.

Substr(column|expression, x, y):- returns specified characters from character value string given as argument -1 at character position x and y characters long.

Example:

The result is world.

Length(column|expression):- the length function returns the number of character in the expression.

Example:

The above example will display to columns of value in the query output output one will consist of all the employee names and the other will display the number of character of each time.

Note:the count starts from 1.
Instr(column|expression, 'str', x, y):- returns the numeric position of a character or word specified in the second argument(str) in the first argument. You can also provide a position x to start searching and the occurrence y of the string. The default value of x and y is 1 means the search starts from the beginning and reports the first occurrence.

Example:

The result is 8.

lpad(column|expression, x, 'str'):- pads the character value passed in the tird argument right justified to a total width of x character position.

Example:

The result is $$$$$15000

The result is $$$$150000
Rpad(column|expression, x, 'str'):- pads the character value passed in the third argument left justified to a total width of x character position.

Example:

The result is $$$$$15000

The result is 15000$$$$$

Trim(leading|trailing|both, trim character from trim source)- enables you to trim heading or trailing characters or both from a character string . in case the trim character or the trim source is a character literal, you must enclose it in single quote.

Example:

The result is elloworld.

replace(string, search string, replacement string)- this function searches a string for a character string. If found replaces it with a specified replacement string.