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.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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.
Method 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.
Method 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]+$'
Method 4
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
Method 5
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. > 0Method 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;Method 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
Method 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
Method 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.
Method 10
This works well for VARCHAR where it begins with a number or not..
WHERE concat('',fieldname * 1) != fieldnamemay have restrictions when you get to the larger NNNNE+- numbers
Method 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
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0