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

User · Answer

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

User · Answer

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

User · Answer

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   gt  0

User · Answer

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

User · Answer

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

User · Answer

Here is the simple solution for it assuming the data type is varchar   select   from calender where year  gt  0   It will return true if the year is numeric else false

User · Answer

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

User · Answer

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

User · Answer

Here is the simple solution for it assuming the data type is varchar   select   from calender where year  gt  0   It will return true if the year is numeric else false

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  0

User · Answer

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

User · Answer

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

User · Answer

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  lt data gt  AS UNSIGNED       LENGTH  lt data gt    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  lt data gt    LENGTH CAST  lt data gt  AS UNSIGNED      CASE WHEN CAST  lt data gt  AS UNSIGNED    0 THEN CAST  lt data gt  AS UNSIGNED  ELSE  LENGTH  lt data gt    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

User · Answer

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

User · Answer

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  lt data gt  AS UNSIGNED       LENGTH  lt data gt    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  lt data gt    LENGTH CAST  lt data gt  AS UNSIGNED      CASE WHEN CAST  lt data gt  AS UNSIGNED    0 THEN CAST  lt data gt  AS UNSIGNED  ELSE  LENGTH  lt data gt    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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql