MySQL return only the rows which are numeric

Question

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

I have been been trying to select only the numeric rows from a field in mysql.

A sample of the data in the database would be:

'1234543' '12f4231' 'fdfrgr34' etc.

I want my select statement to only return the numeric rows.

So in this case the only row I would get back would be the '1234543' row.

Any help would be greatly appreciated.

Thanks, Stefan.

Answer 1

Try this

SELECT * FROM table_name WHERE column_name REGEXP '^[0-9]+$';

Answer 2

You could use a regex, for positive numbers:

SELECT * FROM my_table WHERE my_column REGEXP '^[[:digit:]]+$'

Some adjustments are needed if you want to allow leading/trailing whitespace though.