Detect if value is number in MySQL

mysql sql where-clause
Urbycoz picture Urbycoz · Feb 21, 2011 · Viewed 239.8k times · Source

Is there a way to detect if a value is a number in a MySQL query? Such as

SELECT * 
FROM myTable 
WHERE isANumber(col1) = true

Answer

Thiago Canto picture Thiago Canto · Dec 23, 2011

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

Related questions

SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying …

Read More
SQL WHERE condition is not equal to?

Is it possible to negate a where clause? e.g. DELETE * FROM table WHERE id != 2;

Read More
WHERE vs HAVING

Why do you need to place columns you create yourself (for example select 1 as "number") after HAVING and not WHERE in MySQL? And are there any downsides instead of doing WHERE 1 (writing the whole definition instead of a column name)?

Read More