How do I check to see if a value is an integer in MySQL?

Question

I see that within MySQL there are Cast() and Convert() functions to create integers from values, but is there any way to check to see if a value is an integer? Something like is_int() in PHP is what I am looking for.

Answer 1

I'll assume you want to check a string value. One nice way is the REGEXP operator, matching the string to a regular expression. Simply do

select field from table where field REGEXP '^-?[0-9]+$';

this is reasonably fast. If your field is numeric, just test for

ceil(field) = field

instead.

Answer 2

Match it against a regular expression.

c.f. http://forums.mysql.com/read.php?60,1907,38488#msg-38488 as quoted below:

Re: IsNumeric() clause in MySQL??
Posted by: kevinclark ()
Date: August 08, 2005 01:01PM

I agree. Here is a function I created for MySQL 5:

CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint
RETURN sIn REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

This allows for an optional plus/minus sign at the beginning, one optional decimal point, and the rest numeric digits.

Answer 3

Suppose we have column with alphanumeric field having entries like

a41q
1458
xwe8
1475
asde
9582
.
.
.
.
.
qe84

and you want highest numeric value from this db column (in this case it is 9582) then this query will help you

SELECT Max(column_name) from table_name where column_name REGEXP '^[0-9]+$'

Answer 4

This also works:

CAST( coulmn_value AS UNSIGNED ) // will return 0 if not numeric string.

for example

SELECT CAST('a123' AS UNSIGNED) // returns 0
SELECT CAST('123' AS UNSIGNED) // returns 123 i.e. > 0

Answer 5

Here is the simple solution for it assuming the data type is varchar

select * from calender where year > 0

It will return true if the year is numeric else false

Answer 6

To check if a value is Int in Mysql, we can use the following query. This query will give the rows with Int values

SELECT col1 FROM table WHERE concat('',col * 1) = col;

Answer 7

The best i could think of a variable is a int Is a combination with MySQL's functions CAST() and LENGTH().
This method will work on strings, integers, doubles/floats datatypes.

SELECT (LENGTH(CAST(<data> AS UNSIGNED))) = (LENGTH(<data>)) AS is_int

see demo http://sqlfiddle.com/#!9/ff40cd/44

it will fail if the column has a single character value. if column has a value 'A' then Cast('A' as UNSIGNED) will evaluate to 0 and LENGTH(0) will be 1. so LENGTH(Cast('A' as UNSIGNED))=LENGTH(0) will evaluate to 1=1 => 1

True Waqas Malik totally fogotten to test that case. the patch is.

SELECT <data>, (LENGTH(CAST(<data> AS UNSIGNED))) = CASE WHEN CAST(<data> AS UNSIGNED) = 0 THEN CAST(<data> AS UNSIGNED) ELSE (LENGTH(<data>)) END AS is_int;

Results

**Query #1**

    SELECT 1, (LENGTH(CAST(1 AS UNSIGNED))) = CASE WHEN CAST(1 AS UNSIGNED) = 0 THEN CAST(1 AS UNSIGNED) ELSE (LENGTH(1)) END AS is_int;

| 1   | is_int |
| --- | ------ |
| 1   | 1      |

---
**Query #2**

    SELECT 1.1, (LENGTH(CAST(1 AS UNSIGNED))) = CASE WHEN CAST(1.1 AS UNSIGNED) = 0 THEN CAST(1.1 AS UNSIGNED) ELSE (LENGTH(1.1)) END AS is_int;

| 1.1 | is_int |
| --- | ------ |
| 1.1 | 0      |

---
**Query #3**

    SELECT "1", (LENGTH(CAST("1" AS UNSIGNED))) = CASE WHEN CAST("1" AS UNSIGNED) = 0 THEN CAST("1" AS UNSIGNED) ELSE (LENGTH("1")) END AS is_int;

| 1   | is_int |
| --- | ------ |
| 1   | 1      |

---
**Query #4**

    SELECT "1.1", (LENGTH(CAST("1.1" AS UNSIGNED))) = CASE WHEN CAST("1.1" AS UNSIGNED) = 0 THEN CAST("1.1" AS UNSIGNED) ELSE (LENGTH("1.1")) END AS is_int;

| 1.1 | is_int |
| --- | ------ |
| 1.1 | 0      |

---
**Query #5**

    SELECT "1a", (LENGTH(CAST("1.1" AS UNSIGNED))) = CASE WHEN CAST("1a" AS UNSIGNED) = 0 THEN CAST("1a" AS UNSIGNED) ELSE (LENGTH("1a")) END AS is_int;

| 1a  | is_int |
| --- | ------ |
| 1a  | 0      |

---
**Query #6**

    SELECT "1.1a", (LENGTH(CAST("1.1a" AS UNSIGNED))) = CASE WHEN CAST("1.1a" AS UNSIGNED) = 0 THEN CAST("1.1a" AS UNSIGNED) ELSE (LENGTH("1.1a")) END AS is_int;

| 1.1a | is_int |
| ---- | ------ |
| 1.1a | 0      |

---
**Query #7**

    SELECT "a1", (LENGTH(CAST("1.1a" AS UNSIGNED))) = CASE WHEN CAST("a1" AS UNSIGNED) = 0 THEN CAST("a1" AS UNSIGNED) ELSE (LENGTH("a1")) END AS is_int;

| a1  | is_int |
| --- | ------ |
| a1  | 0      |

---
**Query #8**

    SELECT "a1.1", (LENGTH(CAST("a1.1" AS UNSIGNED))) = CASE WHEN CAST("a1.1" AS UNSIGNED) = 0 THEN CAST("a1.1" AS UNSIGNED) ELSE (LENGTH("a1.1")) END AS is_int;

| a1.1 | is_int |
| ---- | ------ |
| a1.1 | 0      |

---
**Query #9**

    SELECT "a", (LENGTH(CAST("a" AS UNSIGNED))) = CASE WHEN CAST("a" AS UNSIGNED) = 0 THEN CAST("a" AS UNSIGNED) ELSE (LENGTH("a")) END AS is_int;

| a   | is_int |
| --- | ------ |
| a   | 0      |

see demo

Answer 8

What about:

WHERE table.field = "0" or CAST(table.field as SIGNED) != 0

to test for numeric and the corrolary:

WHERE table.field != "0" and CAST(table.field as SIGNED) = 0

Answer 9

I have tried using the regular expressions listed above, but they do not work for the following:

SELECT '12 INCHES' REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$' FROM ...

The above will return 1 (TRUE), meaning the test of the string '12 INCHES' against the regular expression above, returns TRUE. It looks like a number based on the regular expression used above. In this case, because the 12 is at the beginning of the string, the regular expression interprets it as a number.

The following will return the right value (i.e. 0) because the string starts with characters instead of digits

SELECT 'TOP 10' REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$' FROM ...

The above will return 0 (FALSE) because the beginning of the string is text and not numeric.

However, if you are dealing with strings that have a mix of numbers and letters that begin with a number, you will not get the results you want. REGEXP will interpret the string as a valid number when in fact it is not.

Answer 10

This works well for VARCHAR where it begins with a number or not..

WHERE concat('',fieldname * 1) != fieldname 

may have restrictions when you get to the larger NNNNE+- numbers

Answer 11

for me the only thing that works is:

CREATE FUNCTION IsNumeric (SIN VARCHAR(1024)) RETURNS TINYINT
RETURN SIN REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

from kevinclark all other return useless stuff for me in case of 234jk456 or 12 inches