Functions are very powerful feature of SQL which are used to do the followings:
Functions are small purpose programs, which may accept argument and return value. Basically there are two types of SQL functions such as :
Let's see some of the properties of single row functions:
Operates on data items:
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|
Lower(column| expression)- converts character values to lower case.
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.
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.
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.
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.
The result is world.
Length(column|expression):- the length function returns the number of character in the expression.
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.
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.
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.
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.
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.