Detect if value is number in MySQL
MysqlSqlWhere ClauseMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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 2 - Mysql
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 3 - Mysql
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 - Mysql
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
Solution 5 - Mysql
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;
Solution 6 - Mysql
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:]]+$'
Solution 7 - Mysql
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.
Solution 8 - Mysql
Another alternative that seems faster than REGEXP on my computer is
SELECT * FROM myTable WHERE col1*0 != col1;
This will select all rows where col1 starts with a numeric value.
Solution 9 - Mysql
Still missing this simple version:
SELECT * FROM myTable WHERE `col1` + 0 = `col1`
(addition should be faster as multiplication)
Or slowest version for further playing:
SELECT *,
CASE WHEN `col1` + 0 = `col1` THEN 1 ELSE 0 END AS `IS_NUMERIC`
FROM `myTable`
HAVING `IS_NUMERIC` = 1
Solution 10 - Mysql
You can use regular expression for the mor detail https://dev.mysql.com/doc/refman/8.0/en/regexp.html
I used this ^([,|.]?[0-9])+$
. This is allows handle to the decimal and float number
SELECT
*
FROM
mytable
WHERE
myTextField REGEXP "^([,|.]?[0-9])+$"
Solution 11 - Mysql
I recommend: if your search is simple , you can use `
column*1 = column
` operator interesting :) is work and faster than on fields varchar/char
> SELECT * FROM myTable WHERE column*1 = column;
ABC*1 => 0 (NOT EQU **ABC**)
AB15*A => 15 (NOT EQU **AB15**)
15AB => 15 (NOT EQU **15AB**)
15 => 15 (EQUALS TRUE **15**)
Solution 12 - Mysql
SELECT * FROM myTable WHERE sign (col1)!=0
ofcourse sign(0) is zero, but then you could restrict you query to...
SELECT * FROM myTable WHERE sign (col1)!=0 or col1=0
> UPDATE: This is not 100% reliable, because "1abc" would return sign of > 1, but "ab1c" would return zero... so this could only work for text that does not begins with numbers.
Solution 13 - Mysql
> you can do using CAST
SELECT * from tbl where col1 = concat(cast(col1 as decimal), "")
Solution 14 - Mysql
I have found that this works quite well
if(col1/col1= 1,'number',col1) AS myInfo
Solution 15 - Mysql
Try Dividing /1
select if(value/1>0 or value=0,'its a number', 'its not a number') from table