CODEWITHSUNDEEP
mysql
Rpj
Rpj

Reputation: 6080

How to check if a column value contains integers

We have a varchar(255) column and some values are integers, how do I isolate only the rows which contain integer values

Upvotes: 0

Views: 640

Answers (3)

russellc
russellc

Reputation: 494

This should work.

select *
from table
where column
regexp '^\-?[1-9][0-9]*$'

EDIT: thanks Alma Do for pointing out that my solution did not consider signed integers and leading zeroes! Also his solution is much more performant than using regular expressions.

Upvotes: 2

Alma Do
Alma Do

Reputation: 37365

You can do this with CAST():

SELECT * FROM t WHERE CAST(col AS SIGNED)=col

You can use REGEXP() for your issue, but I will not recommend that: for large tables CAST() will be extremely faster. Compare:

mysql> select benchmark(1E7, '17453454.6655744' REGEXP '^[0-9]+$');
+------------------------------------------------------+
| benchmark(1E7, '17453454.6655744' REGEXP '^[0-9]+$') |
+------------------------------------------------------+
|                                                    0 |
+------------------------------------------------------+
1 row in set (17.59 sec)

With:

mysql> select benchmark(1E7, CAST('17453454.6655744' AS SIGNED)='17453454.6655744');
+-----------------------------------------------------------------------+
| benchmark(1E7, CAST('17453454.6655744' AS SIGNED)='17453454.6655744') |
+-----------------------------------------------------------------------+
|                                                                     0 |
+-----------------------------------------------------------------------+
1 row in set, 1 warning (0.36 sec)

-and see the difference.

Upvotes: 2

user3020560
user3020560

Reputation: 59

Here col means, will have to provide column name which has to be checked that having intergers or not ..?

SELECT * FROM t WHERE CAST(col AS SIGNED)=col

Upvotes: 0

Related Questions