Character Functions
ASCII code: every character has a numeric code, known as ASCII code.
This code lies between 0 to 255. MySQL provides us with two functions
associated with it. These are:
13. char(Numeric code): This function is used to find the character of the
corresponding ASCII code. For example,
Select char(65); A
Select char(97); a
Select char(48); 0
14. ASCII(character): It returns the corresponding code of the given
character. For example,
Select ascii(‘a’); 97
Select ascii(‘A’); 65
Select ascii(‘0’); 48
Numeric Functions
This group of functions is used to return various mathematical
calculations. They accept numeric input and return numeric
values. Numeric functions consist of two main sections:
Arithmetic Operations and Mathematical functions
Arithmetic operations include +, -, *, / . For example:
Select 5 * 6; 30
Select 5 + 6; 11
Select name, fee, fee + 1000 from student;
Mathematical Functions: SQL provides a number of
mathematical functions, which is applicable on numeric data.
Numeric Functions
Following are various mathematical functions:
1. Pow(x, y) or Power(x, y): It returns x raised to the power y.
Select pow(2, 4); 16
2. Sqrt(number): It returns square root of the given number.
Select sqrt(49); 7
3. Mod(x, y): It returns the remainder when x is divided by y.
Select mod(10, 7); 3
Select mod(7, 10); 7
4. abs(number): It converts a negative value into positive.
Select abs(-10); 10
Select abs(10); 10
Numeric Functions
5. Truncate(x, d): This function return a numeric value truncated to
a precision (d) decimal places. If d is 0, the result has no decimal
point or fractional part. D can be negative to cause d digits left of
the decimal point of the value x to become zero.
Consider the following examples:
Select truncate(125.223, 1); 125.2
Select truncate(125.223, 2); 125.22
Select truncate(125.223, 3); 125.223
Select truncate(125.223, 0); 125
Select truncate(125.223, -1); 120
Select truncate(125.223, -2); 100
Select truncate(125.223, -3); 0
Numeric Functions
5. Round(x, d): This function is used to remove digits by
round off the digits.
Consider the following examples:
Select round(525.637, 1); 525.6
Select round(525.637, 2); 525.64
Select round(525.637, 3); 525.637
Select round(525.637, 0); 526
Select round(525.637, -1); 530
Select round(525.637, -2); 500
Select round(525.637, -3); 1000
Date & Time Functions
MySQL store date in numeric format, representing the
century, year, month, day, hours, minutes, and seconds.
The default date format in MySQL is YYYY-MM-DD, for
example, 2020-07-11.
Arithmetic with dates: Since dates are stored as numbers, we
can perform the arithmetic calculations using arithmetic
operators. For example:
Operation Result Description
Date + number Date Adds a number of days to date
Date-number Date Subtracts a number of days from a date.
Date-date Number of days Subtracts one date from another
Date & Time Functions
1. sysdate(): It returns current system date & time.
2. now(): It also returns current system date & time.
For example,
Select now(), sysdate();
2020-07-10 11:41:16 2020-07-10 11:41:16
3. curdate(); It returns current system date.
Select curdate(); 2020-07-10
4. Dayname(date): It requires date argument, and returns the name of the
day. For example:
Select curdate(), dayname(curdate());
2020-07-11 Saturday
5. Monthname(date): It returns the name of the month from the given date.
For example:
Select curdate(), monthname(curdate());
2020-07-11 July
Date & Time Functions
6. DayOfWeek(date): It returns day of the week in numeric format as 1 for
Sunday to 7 for Saturday.
Select dayofweek(curdate()); 7
7. DayOfYear(): It returns the day of year for given date in numeric format, in
the range 1 to 366.
Select dayofyear(curdate()); 193
8. Curtime(now()); It returns current system time in the format “HH:MM:SS”.
Select curtime(now()); 11:55:44
9. Day(): It returns the date part of the date expression.
10. Month(): It returns the month part of the date expression.
11. Year(): It returns the year part of the date expression.
For example:
Select day(curdate()), month(curdate()), year(curdate());
11 07 2020
Date & Time Functions
12. hour(date expression): It returns hour part from the time.
13. Minute(date expression): It returns minute part.
14. Second(date expression); It returns second part.
For example:
select hour(now()), minute(now()), second(now());
10 55 42