How to check for numeric values in mysql?

Question

I need to select some data from a table where a column is not a numerical value but can't seem to get the right syntax for this.

Basically we need to do something like this:

SELECT * FROM tablename WHERE column_name (IS NOT A NUMERIC VALUE)

what is the correct syntax to accomplish this?

Answer 1

You could use regular expression to do this, here is the example with the reference site that might help you:

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

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

In regex ^ mean begin, and $ - end.
SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+.?[0-9]*$'; - for 123.12

But, select all records where number exists:

SELECT * FROM myTable WHERE col1 REGEXP '[0-9]+';
Result: 'test0' and 'test1111' and '111test' and '111'

http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql