How to cast from VARCHAR to INT in MySQL?

To cast VARCHAR to INT, we can use the cast() function from MySQL. Here is the syntax of cast() function.

<span class="pln">cast</span><span class="pun">(</span><span class="pln">anyValue </span><span class="kwd">as</span><span class="pln"> dataType</span><span class="pun">)</span>

For our example, we will create a table with the help of CREATE command.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">VarchartointDemo</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> </span><span class="pun">(</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> </span><span class="typ">Value</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">100</span><span class="pun">)</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> </span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.51</span><span class="pln"> sec</span><span class="pun">)</span>

After creating a table, let us insert some records into the table with the help of INSERT command. The query is as follows ?

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">VarchartointDemo</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'123'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.26</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">

mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">VarchartointDemo</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'234'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.16</span><span class="pln"> sec</span><span class="pun">)</span>

Display all records with the help of select statement.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">VarchartointDemo</span><span class="pun">;</span>

The following is the output.

+-------+
| Value |
+-------+
| 123   |
| 234   |
+-------+
2 rows in set (0.00 sec)

Cast varchar to int using the following syntax.

<span class="pln">SELECT CAST</span><span class="pun">(</span><span class="pln">yourColumnName AS anyDataType</span><span class="pun">)</span><span class="pln"> FROM yourTableName</span><span class="pun">;</span>

Apply the above syntax to cast varchar to int.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> SELECT CAST</span><span class="pun">(</span><span class="typ">Value</span><span class="pln"> AS UNSIGNED</span><span class="pun">)</span><span class="pln"> FROM </span><span class="typ">VarchartointDemo</span><span class="pun">;</span>

The following is the output.

+-------------------------+
| CAST(Value AS UNSIGNED) |
+-------------------------+
|                     123 |
|                     234 |
+-------------------------+
2 rows in set (0.00 sec)

Look at the above output, we have changed varchar to int.