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

[{'answer_id': 75739, 'author': 'JBB', 'author_id': 12332, 'author_profile': 'https://Stackoverflow.com/users/12332', 'pm_score': 4, 'selected': False, 'text': '<p>Match it against a regular expression.</p>\n<p>c.f. <a href="http://forums.mysql.com/read.php?60,1907,38488#msg-38488" rel="noreferrer">http://forums.mysql.com/read.php?60,1907,38488#msg-38488</a> as quoted below:</p>\n<blockquote>\n<p>Re: IsNumeric() clause in MySQL??\n<br />Posted by: kevinclark ()\n<br />Date: August 08, 2005 01:01PM</p>\n<p><br />I agree. Here is a function I created for MySQL 5:</p>\n</blockquote>\n\n<pre><code>CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint\nRETURN sIn REGEXP \'^(-|\\\\+){0,1}([0-9]+\\\\.[0-9]*|[0-9]*\\\\.[0-9]+|[0-9]+)$\';\n</code></pre>\n<blockquote>\n<p><br />This allows for an optional plus/minus sign at the beginning, one optional decimal point, and the rest numeric digits.</p>\n</blockquote>\n'}, {'answer_id': 75880, 'author': 'Jumpy', 'author_id': 9416, 'author_profile': 'https://Stackoverflow.com/users/9416', 'pm_score': 9, 'selected': True, 'text': "<p>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</p>\n\n<pre><code>select field from table where field REGEXP '^-?[0-9]+$';\n</code></pre>\n\n<p>this is reasonably fast. If your field is numeric, just test for</p>\n\n<pre><code>ceil(field) = field\n</code></pre>\n\n<p>instead.</p>\n"}, {'answer_id': 5244724, 'author': 'Jayjitraj', 'author_id': 376948, 'author_profile': 'https://Stackoverflow.com/users/376948', 'pm_score': 3, 'selected': False, 'text': '<p>Here is the simple solution for it\nassuming the data type is varchar </p>\n\n<pre><code>select * from calender where year &gt; 0\n</code></pre>\n\n<p>It will return true if the year is numeric else false </p>\n'}, {'answer_id': 10626708, 'author': 'Bill Kelly', 'author_id': 1399626, 'author_profile': 'https://Stackoverflow.com/users/1399626', 'pm_score': 1, 'selected': False, 'text': "<p>I have tried using the regular expressions listed above, but they do not work for the following:</p>\n\n<pre><code>SELECT '12 INCHES' REGEXP '^(-|\\\\+){0,1}([0-9]+\\\\.[0-9]*|[0-9]*\\\\.[0-9]+|[0-9]+)$' FROM ...\n</code></pre>\n\n<p>The above will return <code>1</code> (<code>TRUE</code>), meaning the test of the string '12 INCHES' against the regular expression above, returns <code>TRUE</code>.  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.  </p>\n\n<p>The following will return the right value (i.e. <code>0</code>) because the string starts with characters instead of digits</p>\n\n<pre><code>SELECT 'TOP 10' REGEXP '^(-|\\\\+){0,1}([0-9]+\\\\.[0-9]*|[0-9]*\\\\.[0-9]+|[0-9]+)$' FROM ...\n</code></pre>\n\n<p>The above will return <code>0</code> (<code>FALSE</code>) because the beginning of the string is text and not numeric.</p>\n\n<p>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.</p>\n"}, {'answer_id': 11693466, 'author': 'Tom Auger', 'author_id': 467386, 'author_profile': 'https://Stackoverflow.com/users/467386', 'pm_score': 2, 'selected': False, 'text': '<p>What about:</p>\n\n<pre><code>WHERE table.field = "0" or CAST(table.field as SIGNED) != 0\n</code></pre>\n\n<p>to test for numeric and the corrolary:</p>\n\n<pre><code>WHERE table.field != "0" and CAST(table.field as SIGNED) = 0\n</code></pre>\n'}, {'answer_id': 12577316, 'author': 'Tarun Sood', 'author_id': 1696374, 'author_profile': 'https://Stackoverflow.com/users/1696374', 'pm_score': 4, 'selected': False, 'text': "<p>Suppose we have column with alphanumeric field having entries like</p>\n\n<pre><code>a41q\n1458\nxwe8\n1475\nasde\n9582\n.\n.\n.\n.\n.\nqe84\n</code></pre>\n\n<p>and you want highest numeric value from this db column (in this case it is 9582) then this query will help you</p>\n\n<pre><code>SELECT Max(column_name) from table_name where column_name REGEXP '^[0-9]+$'\n</code></pre>\n"}, {'answer_id': 20761038, 'author': 'Riad', 'author_id': 1957432, 'author_profile': 'https://Stackoverflow.com/users/1957432', 'pm_score': 3, 'selected': False, 'text': "<p>This also works:</p>\n\n<pre><code>CAST( coulmn_value AS UNSIGNED ) // will return 0 if not numeric string.\n</code></pre>\n\n<p>for example</p>\n\n<pre><code>SELECT CAST('a123' AS UNSIGNED) // returns 0\nSELECT CAST('123' AS UNSIGNED) // returns 123 i.e. &gt; 0\n</code></pre>\n"}, {'answer_id': 31694100, 'author': 'minhas23', 'author_id': 2458916, 'author_profile': 'https://Stackoverflow.com/users/2458916', 'pm_score': 3, 'selected': False, 'text': "<p>To check if a value is Int in Mysql, we can use the following query.\nThis query will give the rows with Int values</p>\n\n<pre><code>SELECT col1 FROM table WHERE concat('',col * 1) = col;\n</code></pre>\n"}, {'answer_id': 34655769, 'author': 'PodTech.io', 'author_id': 1842743, 'author_profile': 'https://Stackoverflow.com/users/1842743', 'pm_score': 1, 'selected': False, 'text': "<p>This works well for VARCHAR where it begins with a number or not..</p>\n\n<pre><code>WHERE concat('',fieldname * 1) != fieldname \n</code></pre>\n\n<p>may have restrictions when you get to the larger NNNNE+- numbers</p>\n"}, {'answer_id': 41845958, 'author': 'Tim', 'author_id': 7467766, 'author_profile': 'https://Stackoverflow.com/users/7467766', 'pm_score': 0, 'selected': False, 'text': "<p>for me the only thing that works is:</p>\n\n<pre><code>CREATE FUNCTION IsNumeric (SIN VARCHAR(1024)) RETURNS TINYINT\nRETURN SIN REGEXP '^(-|\\\\+){0,1}([0-9]+\\\\.[0-9]*|[0-9]*\\\\.[0-9]+|[0-9]+)$';\n</code></pre>\n\n<p>from kevinclark all other return useless stuff for me in case of <code>234jk456</code> or <code>12 inches</code></p>\n"}, {'answer_id': 49898031, 'author': 'Raymond Nijland', 'author_id': 2548147, 'author_profile': 'https://Stackoverflow.com/users/2548147', 'pm_score': 2, 'selected': False, 'text': '<p>The best i could think of a variable is a int Is a combination with MySQL\'s functions <code>CAST()</code> and <code>LENGTH()</code>. <br /> \nThis method will work on strings, integers, doubles/floats datatypes.</p>\n\n<pre><code>SELECT (LENGTH(CAST(&lt;data&gt; AS UNSIGNED))) = (LENGTH(&lt;data&gt;)) AS is_int\n</code></pre>\n\n<p>see demo <a href="http://sqlfiddle.com/#!9/ff40cd/44" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/ff40cd/44</a></p>\n\n<blockquote>\n  <p>it will fail if the column has a single character value. if column has\n  a value \'A\' then Cast(\'A\' as UNSIGNED) will evaluate to 0 and\n  LENGTH(0) will be 1. so LENGTH(Cast(\'A\' as UNSIGNED))=LENGTH(0) will\n  evaluate to 1=1 => 1</p>\n</blockquote>\n\n<p>True Waqas Malik totally fogotten to test that case. the patch is. </p>\n\n<pre><code>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;\n</code></pre>\n\n<p><strong>Results</strong></p>\n\n<pre><code>**Query #1**\n\n    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;\n\n| 1   | is_int |\n| --- | ------ |\n| 1   | 1      |\n\n---\n**Query #2**\n\n    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;\n\n| 1.1 | is_int |\n| --- | ------ |\n| 1.1 | 0      |\n\n---\n**Query #3**\n\n    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;\n\n| 1   | is_int |\n| --- | ------ |\n| 1   | 1      |\n\n---\n**Query #4**\n\n    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;\n\n| 1.1 | is_int |\n| --- | ------ |\n| 1.1 | 0      |\n\n---\n**Query #5**\n\n    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;\n\n| 1a  | is_int |\n| --- | ------ |\n| 1a  | 0      |\n\n---\n**Query #6**\n\n    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;\n\n| 1.1a | is_int |\n| ---- | ------ |\n| 1.1a | 0      |\n\n---\n**Query #7**\n\n    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;\n\n| a1  | is_int |\n| --- | ------ |\n| a1  | 0      |\n\n---\n**Query #8**\n\n    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;\n\n| a1.1 | is_int |\n| ---- | ------ |\n| a1.1 | 0      |\n\n---\n**Query #9**\n\n    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;\n\n| a   | is_int |\n| --- | ------ |\n| a   | 0      |\n</code></pre>\n\n<p>see <a href="https://www.db-fiddle.com/f/NNXJ9cPwxjNPz9NknsSGU/0" rel="nofollow noreferrer">demo</a> </p>\n'}]
['https://Stackoverflow.com/questions/75704', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/8224/']