Scaler
Academy
Data Science
Neovarsity
New
Skill Test
CoursesFree Masterclass
Search for Articles, Topics
MySQL trunc

MySQL trunc

By Aditya Trivedi
5 mins read
Last updated: 4 May 2023
147 views
Learn via video courses
Topics Covered

Overview

MySQL TRUNCATE() function is used to obtain an integer part of a numeric expression by discarding its fractional part without any rounding. It allows truncating a numeric value to a defined number of decimal places. Let's understand more about this MySQL TRUNCATE() function in this article ahead.

Syntax of MySQL TRUNCATE() Function

The syntax for using the MySQL TRUNCATE() function is:

Parameters of MySQL TRUNCATE() Function

MySQL TRUNCATE() function takes in two parameters, which are explained below.

X: X is a numeric expression or a literal number that needs to be truncated.

D: D is the number of decimal places to which the number is truncated. When D is a negative number, the TRUNCATE() function replaces D digits to the left of the decimal point of X with zero. If D equals zero, the resulting value has no decimal point.

Both X and D parameters are mandatory for the TRUNCATE() function.

Return Value of MySQL TRUNCATE() Function

The return value of the MySQL TRUNCATE() function is a truncated version of the input number "X". The number of decimal places in the returned value is equal to the "D" parameter.

Exceptions of MySQL TRUNCATE() Function

There are a few exceptions that can occur when using the MySQL TRUNCATE() Function, which are outlined below:

1. Non-numeric value: When a non-numeric value is provided as the first argument to the MySQL TRUNCATE() Function, it will generate an error.

2. Null values: If the input value or the decimal places parameter is NULL, the function will return a syntax error.

3. Overflow error: If the value to be truncated is too large to fit in the specified number of decimal places, then no truncation will be done and the function will return the original value of X.

How does the MySQL TRUNCATE() Function Work?

MySQL TRUNCATE() Function takes 2 arguments that are X and D. X is a numeric expression or a literal number that needs to be truncated and D is the number of decimal places to which the number is truncated, so when the MySQL TRUNCATE() Function is called, it takes the input number "X" and removes all digits to the right of the decimal point, except for the number of digits specified by the second argument "D". As a result a truncated version of the input number "X" is returned.

Examples

Example 1: Truncating a number when D is positive(+ve).

Truncating a positive(+ve) number up to 2 decimal places

Output

In the above example "28757.7128" and "2" are passed as X and D to the MySQL TRUNCATE() Function, by which all the digits of "28757.7128" after 2 decimal values are removed, and 28757.71 is generated as a result.

Truncating a negative(-ve) number up to 3 decimal places

Output

In the above example "-8879.2379684" and "3" are passed as X and D to the MySQL TRUNCATE() Function, by which all the digits of "-8879.2379684" after 3 decimal values are removed and -8879.237 is generated as a result.

Example 2: Truncating a number when D is negative(-ve).

Truncating a positive(+ve) number

Output

In the above example "21997.6597" and "-1" are passed as X and D to the MySQL TRUNCATE() Function, by which the 1 digit before the decimal of "21997.6597" got replaced by 0 because the -ve value of D is passed, finally 21990 is generated as a result.

Truncating a negative(-ve) number

Output

In the above example "-28098.2689" and "-2" are passed as X and D to the MySQL TRUNCATE() Function, by which the 2 digit before the decimal of "-28098.2689" got replaced by 0 because the -ve value of D is passed, finally -28000 is generated as a result.

Example 3: Truncating a number when D is 0.

Truncating a positive(+ve) number

Output

In the above example "221.87" and "0" are passed as X and D to the MySQL TRUNCATE() Function. In this case, the number will be truncated to 0 decimal places, and 221 is generated as a result.

Truncating a negative(-ve) number

Output

In the above example "-110.65" and "0" are passed as X and D to the MySQL TRUNCATE() Function. In this case, the number will be truncated to 0 decimal places, and -110 is generated as a result.

Conclusion

  • MySQL TRUNCATE() function is a valuable tool for any MySQL developer working with numerical data, and understanding how to use it effectively can help you improve the accuracy and efficiency of your applications.
  • This function can be used to truncate a number to a specified number of decimal places or to remove all decimal places entirely.
  • MySQL TRUNCATE() function is also faster and more efficient than using other methods, such as the ROUND() function, which can be more resource-intensive.

See Also

MCQs

MCQ 1

What is the purpose or function of MySQL's TRUNCATE() function?

  1. Removes all data from a specified table
  2. Truncates a number to a specified number of decimal places or removes all decimal places
  3. Rounds a number to the nearest integer
  4. Returns the absolute value of a number

Correct Answer: 2

MCQ 2

Which of these demonstrates the usage of the TRUNCATE() function in MySQL to eliminate all decimal places from a number?

  1. TRUNCATE(6.789, 2)
  2. TRUNCATE(6.789)
  3. TRUNCATE(6.789, 0)
  4. TRUNCATE(6.789, -1)

Correct Answer: 3

MCQ 3

How many parameters are required by MySQL Truncate() function?

  1. 4
  2. 1
  3. 2
  4. 3

Correct Answer: 3