Functions in MySQL

Functions

Functions in MySQL are broadly categorized as:

  • Single row functions: They operate on a single value to return a single value. You can further categorize them into numeric, string, and date and time functions.
  • Multiple row functions: Also known as aggregate functions, they operate on a set of rows and return a single value. Examples include sum(), avg(), count(), etc.

Numeric Functions

They perform operations on numeric values, and also return numeric values.

  • power(a, b) or pow(a, b): It returns the value of a raised to the power of b.
    select pow(2, 3);
    select power(salary, 2) from employee;
  • round(n, d): It rounds the number n to d decimal places.
    select round(5.2381, 2);
    If the number of decimal places is not specified, then it rounds to the nearest integer.
  • truncate(n, d): It returns the number n, truncated to d decimal places.
    select truncate(3.14159, 2);

String or Character Functions

  • length(s): It returns the length of a column or a string in bytes.
    select length('MySQL');
    select length(name) from student;
  • concat(s1, s2): It returns the string formed by concatenating the strings s1 and s2. It may have one or more arguments.
    select concat(first_name, ' ', last_name) from student;
  • instr(s, str): It returns the position of the first occurrence of string str in the string s.
    select instr('information', 'for');
  • lower(s) or lcase(s): It converts the string s into lowercase and returns it.
    select lower(name) from student;
  • upper(s) or ucase(s): It converts the string s into uppercase and returns it.
    select upper(name) from student;
  • left(s, n): It returns n number of characters from the left of the string s.
    select left(name, 3) from student;
  • right(s, n): It returns n number of characters from the right of string s.
    select right(name, 3) from student;
  • ltrim(s): It removes leading spaces from the string s.
    select ltrim(name) from student;
  • rtrim(s): It removes trailing spaces from the string s.
    select rtrim(name) from student;
  • trim(s): It removes both leading and trailing spaces from the string s.
    select trim(name) from student;
  • substring(s, pos, n) or mid(s, pos, n): It returns n number of characters, starting from position pos from the string s.
    select substring('information', 3, 4);
  • ascii(s): It returns the ASCII value of the leftmost character of the string s. Returns 0 if the string is empty. returns null if the string is null.
    select ascii('A');

Date and Time Functions

  1. curdate(): It returns the current date.
    select curdate();
  2. now(): It returns the current date and time.
    select now();
  3. sysdate(): It returns the current date and time at which the function executes.
    select sysdate();
  4. date(d): Extracts the date portion from a date or date-time expression d.
    select date('2018-05-07 10:51:01');
  5. month(d): Returns the numeric month value, ranging from 0 to 12.
    select month(joining) from employee;
  6. year(d): It returns the year from date d, ranging from 0 to 9999.
    select year(joining) from employee;
  7. dayname(d): It returns the daya name from the date d.
    select dayname(joining) from employee;
  8. dayofmonth(d): It returns the day of the month in the range 0 to 31.
    select dayofmonth(joining) from employee;
  9. dayofweek(d): It returns the day of week in number. 1 for Sunday, 2 for Monday and so on.
    select dayofweek(joining) from employee;
  10. dayofyear(d): It returns the day of the year for date d in number, ranging from 1 to 366.
    select dayofyear(joining) from employee;

Leave a Reply

%d bloggers like this: