Detect if value is number in MySQL

Question

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

Solution 1

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)

Solution 2

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

Solution 3

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'

Solution 4

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

Solution 5

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;

View more solutions

Comments

[Jahaziel]

I have tested the 1*col = col strategy, but somehow it fails when the query is called via PHP (returning true when it shouldn´t). In phpMyAdmin however, the hack works. This means my test behaves as expected, buy my application doesn´t.

[Urbycoz]

Thank you. Unfortunately I need it to recognise that 123 is a number, but 123X is not.

[Urbycoz]

@Richard- I just read the exceptions you gave. Thought you meant the character "e". I see what you mean now.

[Dmitriy Kozmenko]

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

[brokethebuildagain]

I like this approach better because it's clearer and less "hackish" than the concatenation trick. Thanks!

[pedromanoel]

The accepted answer is really clever, but this answer is more direct, and I think it should be the accepted solution.

[scrowler]

For the case of "doesn't match": WHERE col1 NOT REGEXP..., and for the case where you might have a decimal point, use regex: ^[0-9\.]+$

[pim]

Leading zeros are not an issue for a deft sql developer --- trim(leading 0 from col1)

[Urbycoz]

What about if the value is zero?

[Urbycoz]

I guess you could just add AND col1<>0 to handle that exception.

[Abbas]

It is true that it doesn't work for zero values but it perfectly works for padded numbers, e.g. 004. The accepted answer does not work for padded numbers

[Binu Raman]

I think this is the best way to check for numbers. It's just that we need to add an OR statement for checking zero, as SELECT * FROM myTable WHERE col1*0 != col1 OR col1='0';

[GRosay]

I know it's an old post but I use this method in my query. But I've a problem, it detects "2-Power" as "2" causing trouble as it's not supposed to do that. Any idea ?

[RichardTheKiwi]

@GaspardRosay What do you mean it detects? sqlfiddle.com/#!9/d8315/2 shows that "2-Power" is not detected as a number, as intended.

[RichardTheKiwi]

@DmitriyKozmenko If "number" includes decimals, then the regexp is no longer "more direct" and a solution is not offerred in comments or the answer.

[François Breton]

For trailing and leading zeros (ex. 023.12000) : concat('', col1 * 1) = '0' OR concat('', col1 * 1) = IF(LOCATE('.', col1), TRIM(BOTH '0' FROM col1), TRIM(LEADING '0' FROM col1));

[David Wilkins]

Also won't work for scientific notation, only works for ints

[Nicolas]

Not working for negative values. I'd amend the proposed regexp as follows : REGEXP '^[+\-]?[0-9]+\\.?[0-9]*$'

[ElChupacabra]

CONCAT('', col1 * 1) is not necessary. CONCAT(col1 * 1) works as well. You don't have to pass 2 agruments to concat() function.

[Grzegorz Smulko]

Are you aware that in MySQL both the select 'aaa123' >= 0 and select '123aaa' >= 0 return true?

[Ivan McA]

Unless I'm misunderstanding, MySQL converts any string to a 0 so this will not distinguish between strings and numbers, both will return the same.

[Paul Spiegel]

'a' + 0 = 'a' is TRUE

[Paul Spiegel]

You don't need to concatenate with an empty string. concat(col1 * 1) = col1 is the same (at least in newer versions). concat(x) works like casting to string.

[Paul Spiegel]

It's the same as to check for col1 <> 0 and gives a false positive for 1a - rextester.com/HLORBZ1242

[Paul Spiegel]

I get a false positive for '1a'. BTW: it's equivalent to WHERE col1 <> 0 - rextester.com/DJIS1493

[Dom]

Perfect! Only answer that actually covers all bases. Should be the accepted answer.

[yoad w]

i know its an old post, but since it still ranks high on google i got here. the method in this answer actually fails on mariadb UPDATE queries, and i suggest using the proposed REGEXP in other answers

[Olli]

Regex migth be hard to read for people that never used it, but you can do really great and short things with it

[Thiago Canto]

I'd say the "+" symbol is not necessary, you could use just a "-?", but if you want to use it, you should escape it (and the "-" symbol doesn't need to be escaped).

[RichardTheKiwi]

@yoadw can you be more specific? What does it fail on in your UPDATE query?

[yoad w]

@RichardTheKiwi i am getting "Error 1292: truncated incorrent DOUBLE value: <value> '. it may be something specific with my db values. don't remember my research results from back then, but i gave up and used the regex approach.

[JStephen]

This does not work, if you have a string that starts with a number "123abc" it will be returned in your numeric rows statement and not in the non-numeric statement.

[Bora]

@JStephen You right! Because SELECT * FROM myTable WHERE col1 = 123; query will return rows even col value is 123abc

[Ferhat KOÇER]

@ Grzegorz Smulko is not right. SELECT 'aaa123'*1 returns to 0 it is not equal to self and SELECT '123aaa'*1 returns to 123 it is not equal to self

[Arghya Sadhu]

Please add some explanation about how this solves the question

[Tudor Corcimar]

also instead of NOT you can use regexp [^0-9]+$ which means not in set of 0-9 symbols