MySQL – Math and Text Functions

MySQL Functions

Functions are commands written with required values as parameters to fulfill various tasks related to calculations or logical comparison or displaying statistics from the database. Consider the following database table for all functions:

SQL Table

Math Functions

Math functions allows to do mathematical computations in MySQL. The functions given in your syllabus are as following:

  1. Power: It is used to compute power of given value as parameter.

Syntax: power(value or column)

Example:

  1. Value: Select power(3,3), the output will be
power function MySQL
  1. Column: select power(rollno, 2) from students;
using power function on table columns in MySQL
  1. Round: It is used to display the number to nearest number with rounding up if the next digit is more than 5.

Syntax: round(value or column, digits)

using round function in MySQL
  1. Mod: This function returns the remainder after division of the number with divisor.

Syntax: select mod(value or column, divisor)

Example: select mod(53,3)m

More functions not given in your syllabus

  1. SQRT: This function returns square root of given number.

Syntax: select sqrt(value or column)

  1. ABS: This function returns the number into positive number as an absolute value.

Syntax: select abs(value or column)

  1. Truncate: This function returns a number after removing specified digits as parameter.

Syntax: select truncate(value or column, digits)

  1. Sign: It will return 1 if the number is positive and returns -1 if the number is negative.

Syntax: select sign(value or number)

sign in MySQL
Sign in MySQL

Text Functions

The text functions are used to manipulate the text based data used in database and display the results.

  1. UCASE/UPPER: This function is used to convert the text into upper case i.e. into capital.

Syntax: select ucase(‘text’ or column) or select upper(‘text’ or column)

ucas-upper in MySQL
  1. Lcase/lower: This function is used to convert the enclosed text into lower case i.e. small letters.

Syntax: select lcase(‘text’ or column) or lower(‘text’ or column)

  1. Mid: This function returns the text starting from a specified number of letters to a specified letter from the enclosed text or column value.

Syntax: select mid(‘Text’ or column, start_pos, end_pos)

Substring and substr is also used for the same.

  1. Length: This function returns the number of letters from the text including white space.

Syntax: select substr(‘text’ or column)

  1. Left: This function is used to return specified left side letters from the enclosed text or column values.

Syntax: select left(‘text’ or column, no_of_characters)

left in MySQL
  1. Right: It is exactly reverse than left, display the result from right side of selected text.

Syntax: right(‘text’ or column, no_of_characters)

  1. Instr(): It will check the specified text from the enclosed text and return a number from where the specified text is starting.

Syntax: select instr(‘text’ or column,’text_to_search’)

insrt in MySQL

Click here to read all contents

Recommended: IP Class 12

Comment Your Views

%d bloggers like this: