How do I check to see if a value is an integer in MySQL?

In MySQL, checking whether a value is an integer can be accomplished through multiple approaches. Below, I'll introduce several common methods, illustrated with examples to demonstrate their usage.

Method 1: Using CAST and FLOOR Functions

We can utilize the CAST function to convert a value to an integer and then apply the FLOOR function to verify if the value remains unchanged before and after conversion. If it does, the original value is an integer.

Example:

Suppose we have a table products with a field price, and we want to determine if the values in the price column are integers.

sql
SELECT price, 
       IF(price = CAST(price AS SIGNED) AND price = FLOOR(price), 'Integer', 'Not Integer') AS CheckResult
FROM products;

This query returns each price value alongside a check result indicating whether it is an integer.

Method 2: Using Regular Expressions

MySQL also supports regular expressions for checking text patterns. We can employ regular expressions to confirm if a value consists exclusively of digits, thereby identifying it as an integer.

Example:

Continuing with the products table.

sql
SELECT price,
       IF(price REGEXP '^-?[0-9]+$' , 'Integer', 'Not Integer') AS CheckResult
FROM products;

Here, ^-?[0-9]+$ is a regular expression used to validate if a string represents an integer. The expression is explained as follows:

• ^ and $ denote the start and end of the string.
• -? indicates that a negative sign may appear zero or one time.
• [0-9]+ specifies one or more digits.

Method 3: Using DIV 1

This method checks if a value is an integer by dividing it by 1 and comparing the original value with the result.

Example:

We continue using the products table to demonstrate this approach.

sql
SELECT price,
       IF(price = price DIV 1, 'Integer', 'Not Integer') AS CheckResult
FROM products;

DIV performs integer division. If price is an integer, then price DIV 1 will equal price itself.

By employing these methods, we can effectively verify if a value is an integer in MySQL. The choice of method depends on specific requirements and personal preference. Each method has its own applicable scenarios and trade-offs.