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

To check if the given value is a string or not ,we use the cast() function. If the value is not numeric then it returns 0, otherwise it will return the numeric value. In this way, we can check whether the value is an integer or not.

Case 1 − Checking for a string with integers

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

The following is the output. It shows that the value is not numeric, therefore 0 is returned.

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

Case 2 − Checking for only integer values

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

The following is the output. It shows that the value is numeric, therefore the value itself is returned.

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

This logic does work well for float as well.

The following is the query with float value.

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

Here is the output.

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

Alternate logic with the regular operator

It works for all conditions for any value, even float.

Let us create a new table.

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

Inserting records into 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)

To display all records.

mysql> select *from CheckingIntegerDemo;

Here 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 remaining are not.

Syntax to check if the value is an integer.

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

The query wherein we have used regular expression. This will output only the integer value.

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

The following is the output.

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