Detect if value is number in MySQL

You can use Regular Expression too... it would be like:

SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+$';

Reference: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

This should work in most cases.

SELECT * FROM myTable WHERE concat('',col1 * 1) = col1

It doesn't work for non-standard numbers like

• 1e4
• 1.2e5
• 123. (trailing decimal)

If your data is 'test', 'test0', 'test1111', '111test', '111'

To select all records where the data is a simple int:

SELECT * 
FROM myTable 
WHERE col1 REGEXP '^[0-9]+$';

Result: '111'

(In regex, ^ means begin, and $ means end)

To select all records where an integer or decimal number exists:

SELECT * 
FROM myTable 
WHERE col1 REGEXP '^[0-9]+\\.?[0-9]*$'; - for 123.12

Result: '111' (same as last example)

Finally, to select all records where number exists, use this:

SELECT * 
FROM myTable 
WHERE col1 REGEXP '[0-9]+';

Result: 'test0' and 'test1111' and '111test' and '111'