[mysql] Detect if value is number in MySQL

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

This question is related to mysql sql where-clause

The answer is


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


Try Dividing /1

select if(value/1>0 or value=0,'its a number', 'its not a number') from table

I have found that this works quite well

if(col1/col1= 1,'number',col1) AS myInfo

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;

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**)

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.


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])+$"

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:]]+$'

you can do using CAST

  SELECT * from tbl where col1 = concat(cast(col1 as decimal), "")

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


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'


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

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.


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.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to where-clause

Laravel where on relationship object MySQL Select last 7 days SQLSTATE[42S22]: Column not found: 1054 Unknown column - Laravel Conditional WHERE clause in SQL Server Conditional WHERE clause with CASE statement in Oracle How to write a SQL DELETE statement with a SELECT statement in the WHERE clause? MySQL Multiple Where Clause How to use If Statement in Where Clause in SQL? MySQL direct INSERT INTO with WHERE clause MySql Inner Join with WHERE clause