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:

students
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
using round function in MySQL2
using round function in MySQL3
  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

using mod function in MySQL

More functions not given in your syllabus

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

Syntax: select sqrt(value or column)

using SQRT function in MySQL
using SQRT function in MySQL2
  1. ABS: This function returns the number into positive number as an absolute value.

Syntax: select abs(value or column)

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

Syntax: select truncate(value or column, digits)

truncate in MySQL 3
  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)

lcase lower in MySQL
  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)

mid in MySQL

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)

len in pySQL
  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)

right in MySQL
  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: