Detect if value is number in MySQL

Question

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'

SELECT * FROM myTable
WHERE col1 REGEXP '^[+-]?[0-9]*([0-9]\\.|[0-9]|\\.[0-9])[0-9]*(e[+-]?[0-9]+)?$'

Will also match signed decimals (like -1.2, +0.2, 6., 2e9, 1.2e-10).

Test:

drop table if exists myTable;
create table myTable (col1 varchar(50));
insert into myTable (col1) 
  values ('00.00'),('+1'),('.123'),('-.23e4'),('12.e-5'),('3.5e+6'),('a'),('e6'),('+e0');

select 
  col1,
  col1 + 0 as casted,
  col1 REGEXP '^[+-]?[0-9]*([0-9]\\.|[0-9]|\\.[0-9])[0-9]*(e[+-]?[0-9]+)?$' as isNumeric
from myTable;

Result:

col1   |  casted | isNumeric
-------|---------|----------
00.00  |       0 |         1
+1     |       1 |         1
.123   |   0.123 |         1
-.23e4 |   -2300 |         1
12.e-5 | 0.00012 |         1
3.5e+6 | 3500000 |         1
a      |       0 |         0
e6     |       0 |         0
+e0    |       0 |         0

Demo

Returns numeric rows

I found the solution with following query and works for me:

SELECT * FROM myTable WHERE col1 > 0;

This query return rows having only greater than zero number column that col1

Returns non numeric rows

if you want to check column not numeric try this one with the trick (!col1 > 0):

SELECT * FROM myTable WHERE !col1 > 0;

This answer is similar to Dmitry, but it will allow for decimals as well as positive and negative numbers.

select * from table where col1 REGEXP '^[[:digit:]]+$'

use a UDF (user defined function).

CREATE FUNCTION isnumber(inputValue VARCHAR(50))
  RETURNS INT
  BEGIN
    IF (inputValue REGEXP ('^[0-9]+$'))
    THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END;

Then when you query

select isnumber('383XXXX') 

--returns 0

select isnumber('38333434') 

--returns 1

select isnumber(mycol) mycol1, col2, colx from tablex; -- will return 1s and 0s for column mycol1

--you can enhance the function to take decimals, scientific notation , etc...

The advantage of using a UDF is that you can use it on the left or right side of your "where clause" comparison. this greatly simplifies your SQL before being sent to the database:

 SELECT * from tablex where isnumber(columnX) = isnumber('UnkownUserInput');

hope this helps.