SQL function- single row

MySQL Functions

    A function is a special type of predefined command set that performs some operation and may return a single value. MySQL supports functions that can be used to manipulate data. Such functions can be used to manipulate data. Single-row functions return a single result row for every row of a queried table. They are categorized into: Numeric functions, String functions, and Date and Time functions.

Numeric Functions
POWER() : Returns the argument raised to the specified power. POW () works the same way.
Example: (i) POW(2,4); Result:16 (ii) POW(2,-2); Result:0.25 (iii) POW(-2,3) Result: -8

ROUND() : ROUND(X) Rounds the argument to the zero decimal place, Where as ROUND(X,d) Rounds the argument to d decimal places.
Example :     (i) ROUND(-1.23); Result: -1 (ii) ROUND(-1.58); Result: -2 (iii) ROUND(1.58); Result: 2 (iv) ROUND(3.798, 1); Result: 3.8 (v) ROUND(1.298, 0); Result: 1    (vi) ROUND(23.298, -1); Result: 20

TRUNCATE() : Truncates the argument to specified number of decimal places.
Example:     (i) TRUNCATE(7.29,1)Result: 7.2 (ii) TRUNCATE(27.29,-1) Result: 20

Character/String Functions
LENGTH() : Returns the length of a string in bytes/no.of characters in string.
Example:     LENGTH(‘INFORMATICS’); Result:11

CHAR() : Returns the corresponding ASCII character for each integer passed.
Example :     CHAR(65) ; Result : A

CONCAT(): Returns concatenated string i.e. it adds strings.
Example :     CONCAT(‘Informatics’,’ ‘,‘Practices’); Result : Informatics Practices’

INSTR(): Returns the index of the first occurrence of substring.
Example :     INSTR(‘Informatics’,’ mat’);     
Result : 6(since ‘m’ of ‘mat’ is at 6th place)

LOWER()/ LCASE(): Returns the argument after converting it in lowercase.
Example:     LOWER(‘INFORMATICS’);     Result : informatics

UPPER()/ UCASE(): Returns the argument after converting it in uppercase.
Example:     UCASE(‘informatics’); Result : INFORMATICS

LEFT() : Returns the given number of characters by extracting them from the left side of the given string.
Example :    LEFT(‘INFORMATICS PRACTICES’, 3);     Result : INF

RIGHT():Returns the given number of characters by extracting them from the right side of the given string.
Example :     RIGHT(‘INFORMATICS PRACTICES’,3);     Result : CES

MID()/SUBSTR() : Returns a substring starting from the specified position in a given string.
Example:     MID(‘INFORMATICS PRACTICES’,3,4);    Result : FORM

LTRIM() : Removes leading spaces.
Example :     LTRIM(' INFORMATICS')’    Result: 'INFORMATICS’

RTRIM(): Removes trailing spaces.
Example :     RTRIM('INFORMATICS ');    Result: 'INFORMATICS’

TRIM() : Removes leading and trailing spaces.
Example:     TRIM(' INFORMATICS ');    Result: 'INFORMATICS’

Date/Time Functions
 CURDATE() : Returns the current date
Example:    CURDATE();        Result: '2010-07-21'
NOW() :     Returns the current date and time
Example:    NOW();        Result : '2010-07-21    13:58:11'
SYSDATE() : Return the time at which the function executes ,

 Example:    SYSDATE();        Result: '2010-07-21 13:59:23’
 DATE() :     Extracts the date part of a date or datetime expression
Example:    DATE('2003-12-31 01:02:03');    Result:: '2003-12-31'
MONTH()     Returns the month from the date passed
Example:    MONTH('2010-07-21');    Result : 7
YEAR() :    Returns the year YEAR('2010-07-21');     Result : 2010

DAYNAME() :    Returns the name of the weekday
Example:    DAYNAME('2010-07-21');     Result : WEDNESDAY
DAYOFMONTH() : Returns the day of the month (0-31)
Example:    DAYOFMONTH('2010-07-21');    Result: 21
DAYOFWEEK() : Returns the weekday index of the argument
Example:    DAYOFWEEK('2010-07-21'); Result: 4 (Sunday is counted as 1)
DAYOFYEAR() :     Return the day of the year(1-366)
Example:    DAYOFYEAR('2010-07-21');     Result: 202