Functions in MySql
What is Function?
Function is set of statements which execute when it is called and perform a task. We can call them again and again in the program when we require.
In SQL, there are Built-in Functions. Built-in functions are those functions which are already defined in the language.We also call them as Predefined Functions.
Types of Functions?
- Scalar or Single row functions
- Aggregate or group functions
- sum()
- avg()
- count()
- min()
- max()
Scalar or Single row functions
Scalar function is the built-in function in SQL. It works on a single row or a single value at a time.
String Functions
UPPER OR UCASE: Converts a string to upper-case.
Example:
select upper('compuhelp') "UPPER";
+------------+ | UPPER | +------------+ | COMPUHELP | +------------+
or
select ucase(name) from student;
+-------------+ | ucase(name) | +-------------+ | NEHA | | PRIYA | | VANSHIKA | | RITU | | SANJANA | | SIYA | +-------------+
LOWER OR LCASE: Converts a string to lower-case.
Example:
select lower('COMPUHELP') "LOWER";
+-----------+ | LOWER | +-----------+ | compuhelp | +-----------+
or
select lcase(name) from student;
+------------+ | lcase(name)| +------------+ | neha | | priya | | vanshika | | ritu | | sanjna | | siya | +------------+
ASCII: Returns the ASCII value for the specific character.
Example:
select ascii('A') "A",ascii('a') "a",ascii('0') "0";
+----+----+----+ | A | a | 0 | +----+----+----+ | 65 | 97 | 48 | +----+----+----+
CHR: Returns the character based on the ASCII code.
Example:
select chr(65) "65",chr(97) "97",chr(48) "48";
+------+----+----+ | 65 | 97 | 48 | +------+----+----+ | A | a | 0 | +------+----+----+
CONCAT: Adds two or more strings together.
Example:
select concat(name,' ',lastname) "Full Name" from student;
+------------------+ | Full Name | +------------------+ | priya guleria | | vanshika guleria | | ritu guleria | | sanjana guleria | | riya guleria | | siya guleria | +------------------+
LENGTH: Returns the length of a string.
Example:
select length('Compuhelp') "Length";
+--------+ | Length | +--------+ | 9 | +--------+
LEFT: The LEFT() function extracts a number of characters from a string (starting from left).
Example:
select left('compuhelp',5);
+---------------------+
| left('compuhelp',5) |
+---------------------+
| compu |
+---------------------+
or
select left(name,3) from student;
+-------------+ | left(name,3)| +-------------+ | neh | | pri | | van | | rit | | san | +-------------+
RIGHT: The RIGHT() function extracts a number of characters from a string (starting from right).
Example:
select right('compuhelp',4);
+---------------------+
| right('compuhelp',4) |
+---------------------+
| help |
+---------------------+
MID(string,starting value,[length]) : The MID() extracts a substring from a string (starting at any position).
Example:
select mid('compuhelp pvt ltd',11,3) "MID";
+-----+ | MID | +-----+ | pvt | +-----+
SUBSTR or SUBSTRING : The SUBSTR() extracts a substring from a string (starting at any position).
Syntax: substr(string,starting position);
Example:
select substr('welcome to compuhelp coaching',12) "substr";
+--------------------+ | substr | +--------------------+ | compuhelp coaching | +--------------------+
SUBSTR or SUBSTRING : The SUBSTR() extracts a substring from a string (starting at any position).
Syntax: substr(string,starting position);
Example:
select substr('welcome to compuhelp coaching',12) "substr";
+--------------------+ | substr | +--------------------+ | compuhelp coaching | +--------------------+
or
Syntax: substr(string,starting position,length of string);
select substr('welcome to compuhelp coaching',12,9) "substr";
+-----------+ | substr | +-----------+ | compuhelp | +-----------+
TRIM : The TRIM() removes leading and trailing spaces from a string.
Example:
select trim(' compuhelp ') "TRIM";
+-----------+ | TRIM | +-----------+ | compuhelp | +-----------+
LTRIM :The LTRIM() removes leading spaces from a string.
Example:
select ltrim(' compuhelp ') "LTRIM";
+------------+ | LTRIM | +------------+ | compuhelp | +------------+
RTRIM :The RTRIM() removes trailing spaces from a string.
Example:
select rtrim(' compuhelp ') "RTRIM";
+-----------------+ | RTRIM | +-----------------+ | compuhelp | +-----------------+
LPAD : The LPAD() Left-pads a string with another string,to a certain length.
Example:
select lpad(name,10,'*') "Leftpad Student Name" from student ;
+----------------------+ | Leftpad Student Name | +----------------------+ | *****priya | | **vanshika | | ******ritu | | ***sanjana | | ******riya | | ******siya | +----------------------+
RPAD :The RPAD() Right-pads a string with another string, to a certain length.
Example:
select rpad(name,10,'#') "Rightpad Student Name" from student ;
+-----------------------+ | Rightpad Student Name | +-----------------------+ | priya##### | | vanshika## | | ritu###### | | sanjana### | | riya###### | | siya###### | +-----------------------+
REPEAT : Repeats a string as many times as specified.
Example:
select repeat('compuhelp ',3) "Repeat String";
+--------------------------------+ | Repeat String | +--------------------------------+ | compuhelp compuhelp compuhelp | +--------------------------------+
REPLACE : Replaces all occurrences of a substring within a string, with a new substring.
Example:
select replace('computer','ter','help') "Replace";
+-----------+ | Replace | +-----------+ | compuhelp | +-----------+
REVERSE : Reverses a string and returns the result.
Example:
select reverse('welcome to compuhelp') "Reverse";
+-----------------------+ | Reverse | +-----------------------+ | plehupmoc ot emoclew | +-----------------------+
INSTR : Returns the position of the first occurrence of a string in another string.
Example:
select instr('compuhelp','help') "MatchPosition";
+---------------+ | MatchPosition | +---------------+ | 6 | +---------------+
Numeric Functions
ABS(x) : Returns the absolute value of a number.
Example:
select abs(-2024) ;
+------------+ | abs(-2024) | +------------+ | 2024 | +------------+
ROUND(x,d) : Rounds a number to a specified number of decimal places.
Example:
select round(23.765),round(23.765,1);
+---------------+----------------+ | round(23.765) | round(23.765,1)| +---------------+----------------+ | 24 | 23.8| +---------------+----------------+
CEIL(x) or CEILING(x):Returns the smallest integer value greater than or equal to a number.
Example:
select ceil(45.456);
+--------------+ | ceil(45.456) | +--------------+ | 46 | +--------------+
FLOOR(x):Returns the largest integer value less than or equal to a number.
Example:
select floor(45.456);
+---------------+ | floor(45.456) | +---------------+ | 45 | +---------------+
MOD(x, y):Returns the remainder of a number divided by another number.
Example:
select mod(10,3) "Remainder";
+-----------+ | Remainder | +-----------+ | 1 | +-----------+
RAND():Returns a random floating-point number between 0 and 1.
Example:
select rand();
+--------------------+ | rand() | +--------------------+ | 0.9793253009946321 | +--------------------+
POWER(x, y) or POW(x, y):Returns the value of x raised to the power of y.
Example:
select power(2,5);
+------------+ | power(2,5) | +------------+ | 32 | +------------+
SQRT(x):Returns the square root of a number.
Example:
select sqrt(144);
+-----------+ | sqrt(144) | +-----------+ | 12 | +-----------+
SIGN(x):Returns the sign of a number: -1 if negative, 0 if zero, and 1 if positive.
Example:
select sign(-34),sign(10),sign(0);
+-----------+----------+---------+ | sign(-34) | sign(10) | sign(0) | +-----------+----------+---------+ | -1 | 1 | 0 | +-----------+----------+---------+
PI():Returns the value of pi (π).
Example:
select pi();
+----------+ | pi() | +----------+ | 3.141593 | +----------+
Date Functions
CURDATE() or CURRENT_DATE():Returns the current date.
Example:
select curdate();
+------------+ | curdate() | +------------+ | 2024-03-07 | +------------+
NOW():Returns the current date and time.
Example:
select now();
+---------------------+ | now() | +---------------------+ | 2024-03-07 20:43:06 | +---------------------+
CURTIME(): Returns the current time.
Example:
select curtime();
+-----------+ | curtime() | +-----------+ | 20:44:27 | +-----------+
DATE_FORMAT(date, format):Formats a date as specified.
Example:
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y');
+-------------------------------------+ | DATE_FORMAT(NOW(), '%W, %M %e, %Y') | +-------------------------------------+ | Friday, March 8, 2024 | +-------------------------------------+
Common Format Specifiers:
%Y: Year (4 digits)
%y: Year (2 digits)
%m: Month (01..12)
%c: Month (1..12)
%d: Day of the month (01..31)
%e: Day of the month (1..31)
%H: Hour (00..23)
%h: Hour (01..12)
%i: Minutes (00..59)
%s: Seconds (00..59)
%p: AM or PM
%T: Time in 24 hour format (hh:mm:ss)
%W: Weekday name in full (Sunday to Saturday)
%w: Day of the week where Sunday=0 and Saturday=6
DATEDIFF(date1, date2):Returns the difference between two dates in days.
Example:
SELECT DATEDIFF('2024-03-15', '2024-03-08') "Date Difference";
+-----------------+ | Date Difference | +-----------------+ | 7 | +-----------------+
DAY(date):Returns the day of the month for a given date.
Example:
select day(CURDATE());
+----------------+ | day(CURDATE()) | +----------------+ | 8 | +----------------+
DAYNAME(date):Return the weekday name for a date.
Example:
SELECT DAYNAME("2024-03-08");
+-----------------------+
| DAYNAME("2024-03-08") |
+-----------------------+
| Friday |
+-----------------------+
MONTHNAME(date):Returns the name of the month for a given date.
Example:
SELECT MONTHNAME('2024-03-08') "Month Name";
+------------+ | Month Name | +------------+ | March | +------------+
DATE_ADD(date, INTERVAL):Adds a specified time interval to a date.
Example:
SELECT DATE_ADD('2024-03-08', INTERVAL 10 DAY) "Add Time Interval";
+-------------------+ | Add Time Interval | +-------------------+ | 2024-03-18 | +-------------------+
DATE_SUB(date, INTERVAL):Subtracts a specified time interval to a date.
Example:
SELECT DATE_SUB('2024-03-08', INTERVAL 10 DAY) "Subtract Time Interval";
+------------------------+ | Subtract Time Interval | +------------------------+ | 2024-02-27 | +------------------------+
YEAR(date):Returns the year for a given date.
Example:
SELECT YEAR('2024-03-08') "Year";
+------+ | Year | +------+ | 2024 | +------+
PERIOD_ADD(period, number):Adds a specified number of months to a period.
Example:
SELECT PERIOD_ADD(202403, 5);
+-----------------------+ | PERIOD_ADD(202403, 5) | +-----------------------+ | 202408 | +-----------------------+
Parameter Values
| Parameter | Description |
|---|---|
| period | Required. A period. Format: YYMM or YYYYMM |
| number | Required. The number of months to add to period. Both positive and negative values are allowed |
An Aggregate or Group Function
An aggregate function is a function that performs a calculation on a set of values, and returns a single value.
COUNT(x): Counts the number of rows in a result set.
Example:
SELECT COUNT(*) FROM STUDENT;
+----------+ | COUNT(*) | +----------+ | 6| +----------+
SUM(): Returns the sum of values of a numeric column.
Example:
select sum(fee) from student;
+----------+ | sum(fee) | +----------+ | 115600 | +----------+
AVG():Returns the average value of a numeric column.
Example:
select avg(fee) from student;
+-------------+ | avg(fee) | +-------------+ | 19266.6667 | +-------------+
MIN():Returns the minimum value in a column.
Example:
select min(fee) from student;
+-----------+ | min(fee) | +-----------+ | 3000| +-----------+
MAX():Returns the maximum value in a column.
Example:
select max(fee) from student;
+-----------+ | max(fee) | +-----------+ | 55000| +-----------+