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

To check if a given value is a string, we use the cast() function. Returns 0 if the value is not a number, otherwise returns a numeric value. This way we can check if the value is an integer.

Case 1 - Checking a string with an integer

mysql> select cast('John123456' AS UNSIGNED);

The following is the output. Indicates that the value is not a number, so 0 is returned.

+--------------------------------+
| cast('John123456' AS UNSIGNED) |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

Case 2 - Checking only integer values

mysql> select cast('123456' AS UNSIGNED);

The following is the output. It indicates that the value is a number, so the value itself is returned.

+----------------------------+
| cast('123456' AS UNSIGNED) |
+----------------------------+
|                     123456 |
+----------------------------+
1 row in set (0.00 sec)

This logic also works well for floats.

The following is a query for floating point values.

mysql>  SELECT CAST('78.90' AS UNSIGNED);

This is the output.

+---------------------------+
| CAST('78.90' AS UNSIGNED) |
+---------------------------+
|                        78 |
+---------------------------+
1 row in set, 1 warning (0.00 sec)

Alternative logic with regular operators

It works for all conditions with any value, even floating point numbers.

Let's create a new table.

mysql> create table CheckingIntegerDemo
   -> (
   -> Value varchar(200)
   -> );
Query OK, 0 rows affected (0.88 sec)

Insert records into the table.

mysql> insert into CheckingIntegerDemo values('John123456');
Query OK, 1 row affected (0.10 sec)

mysql>  insert into CheckingIntegerDemo values('123456');
Query OK, 1 row affected (0.16 sec)

mysql> insert into CheckingIntegerDemo values('123.456');
Query OK, 1 row affected (0.16 sec)

Display all records.

mysql> select *from CheckingIntegerDemo;

This is the output.

+------------+
| Value      |
+------------+
| John123456 |
| 123456     |
| 123.456    |
+------------+
3 rows in set (0.00 sec

In the above output, only 123456 is an integer, and the rest are not integers.

Syntax for checking whether a value is an integer.

select yourColumnName from yourTableName where yourColumnName REGEXP '^-?[0-9]+$';

We use regular expression queries. This will only output integer values.

mysql> select Value from CheckingIntegerDemo where Value REGEXP '^-?[0-9]+$';

The following is the output.

+--------+
| Value  |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

The above is the detailed content of How to check if a value is an integer in MySQL?. For more information, please follow other related articles on the PHP Chinese website!