SQL FUNCTION

By:

Neeraj Namdev

PGT(CS)

JNV Pathnamthitta

​

SQL Functions�

A function is a predefined set of commands that performs specific task or operation on data returns a value.

SQL functions are also set of command which are applied on relations to perform a operation.�The MySql functions broadly divided into two categories.

Single Row functions (Scalar functions) Multiple Row functions (group or aggregate functions)

Single Row Functions

• The single row functions work with each row at a time and return one result per row or you can say that a relation have n number of rows than it will return n number of values.

e.g.

• Maths functions
• String functions
• Date & Time functions

​

​

Multiple Row Functions

The Multiple row functions work with more than one row at a time and return one result or you can say that a relation have n number of rows than it will return aggregate values.

e.g.

• aggregate functions (Min(), Max(), Avg(), Count(), Sum())

Maths functions

Mathematical functions are also called number functions that take numeric value as a input and return numeric value as a output.

​

Mathematical functions are…

• Round()
• Trunc()
• Mod()
• Power()

Round()

The ROUND() function rounds a number to a specified number of decimal places.

​

• ROUND(number, decimals)

�

Truncate()�

MySQL TRUNCATE() returns a number after truncated to certain decimal places. The number and the number of decimal places are specified as arguments of the TRUNCATE function.

​

Mod()�

MySQL MOD() returns the remainder of a number divided by another number. This function also works on fractional values and returns the exact remainder. The function returns NULL when the value of divisor is 0.

​

​

​

Example :

Power()�

MySQL POWER() returns the value of a number raised to the power of another number.

The synonym of POWER() is POW()

POWER( m, n )

M Numeric value. It is the base used in the calculation.

N Numeric value. It is the exponent used in the calculation.

Example :

String functions

String functions that take string as a input and return String/Numeric value as a output depend on function.

Functions are…

• UCASE ()/UPPER (),
• LCASE ()/LOWER (),
• MID ()/SUBSTRING ()/SUBSTR (),
• LENGTH (),
• LEFT (),
• RIGHT (),
• INSTR (),
• LTRIM (),
• RTRIM (),
• TRIM ().

UCASE ()/UPPER () �

Returns the string with all characters changed to uppercase according to the current character set mapping.

​

​

LCASE ()/LOWER ()�

Returns the string with all characters changed to lowercase according to the current character set mapping.

​

MID ()/SUBSTRING ()/SUBSTR () �

• The forms of function (without a len argument) return a substring from string str starting at position pos upto last character of string str.
• The forms of function (with a len argument) return a substring of length len characters long from string str, starting at position pos.
• (It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function).
• SUBSTRING(str,pos) [SUBSTRING(str FROM pos)]
• SUBSTRING(str,pos,len)[SUBSTRING(str FROM pos FOR len)]

​

Example:

INSTR () �

Returns the position of the first occurrence(position) of substring substr in string str and if substring is not a part of string than return 0.

​

Length()

Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes.

​

Left()

Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

​

Right()

Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

​

Ltrim()

LTRIM() removes the leading space characters of a string passed as an argument. A string whose leading space characters are to be removed.

Rtrim()

RTRIM() removes the trailing space characters of a string passed as an argument. A string whose trailing space characters are to be removed. 

Trim()

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifies BOTH, LEADING, or TRAILING is given, BOTH is assumed.

​

Ascii()

Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for characters with numeric values from 0 to 255.

​

Example:

CHAR()

CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.

​

Date and Time Functions�

Date functions operate on values of the DATE and TIME data type:

Function are….

• Curdate()
• Day()
• Date()
• Month()
• Year()
• Dayname()
• Monthname()
• Dayofweek()
• Dayofmonth()
• Dayofyear()
• Sysdate()
• Now()

Curdate()�

In MySQL the CURDATE() returns the current date in 'YYYY-MM-DD' format or 'YYYYMMDD' format depending on whether numeric or string is used in the function.

​

Day()�

MySQL DAY() returns the day of the month for a specified date. The day returned will be within the range of 1 to 31.

​

Date()

MySQL DATE() returns the DATE part out from a datetime expression.

​

Month()

MySQL MONTH() returns the MONTH for the date within a range of 1 to 12 ( January to December). It Returns 0 when MONTH part for the date is 0.

​

YEAR()

MySQL YEAR() returns the year for a given date. The return value is in the range of 1000 to 9999 or 0 for 'zero' date.

​

Dayname()�

MySQL DAYNAME() returns the name of the week day of a date specified in the argument.

​

Monthname()�

MySQL MONTHNAME() returns the full name of the month for a given date. The return value is within the range of 1 to 12 ( January to December). It Returns NULL when month part for the date is 0 or more than 12.

​

​

Dayofweek()�

MySQL DAYOFWEEK() returns the week day number (1 for Sunday,2 for Monday …… 7 for Saturday ) for a date specified as argument.

​

Dayofmonth()�

MySQL DAYOFMONTH() returns the day of the month for a given date. The day returned will be within the range of 1 to 31. If the date is ‘0000-00-00’, the function will return 0. The DAY() is the synonym of DAYOFMONTH().

Dayofyear()�

MySQL DAYOFYEAR() returns day of the year for a date. The return value is within the range of 1 to 366.

​

Sysdate()�

MySQL SYSDATE() returns the current date and time in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS. format depending on the context of the function.

​

Now()

MySQL NOW() returns the value of current date and time in ‘YYYY-MM-DD HH:MM:SS’ format or YYYYMMDDHHMMSS. format depending on the context (numeric or string) of the function.

​

Difference b/w �Now() & Sysdate()

Aggregate functions�

An aggregate function performs a calculation on multiple values and returns a single value.

Functions are…

• Min()
• Max()
• Avg()
• Count()
• Sum()

​

Table used�for Aggregate function

Min()�

Return the lowest value (minimum) in a set of non-NULL values.

​

Max()

Return the highest value (maximum) in a set of non-NULL values.

​

Avg()

Return the average of non-NULL values.

​

Sum()

Return the summation of all non-NULL values a set.

​

Count()

Return the number of rows in a group, including rows with NULL values. But when we apply on single value it will not consider null values.

​

​

Thanks