Cast from VARCHAR to INT - MySQL

The how-to of casting

Let's dig a bit deeper into the art and science of converting VARCHAR to INT in MySQL.

Implicit casting: Using arithmetic

The typical CAST or CONVERT methods are not the only options for data type casting. There's also the smart hack of performing an arithmetic operation to implicitly cast your VARCHAR to INT. Multiply by 1 as follows: (varchar_col * 1). Now you're converting data types without using any functions!

SELECT varchar_col * 1 AS int_col FROM table;


// Who needs functions when you've got multiplication on your side? 💪

Unsigned for the win: Positive INT

You can further fine-tune your conversion by opting for UNSIGNED when you know your values will always be positive:

SELECT CAST(varchar_col AS UNSIGNED) FROM table;


// Is negativity affecting your conversion? Bring positivity with UNSIGNED!

Versioning matters: MySQL 5.5.16

Beware! MySQL versions under 5.5.16 may have their own peculiar behaviors when it comes to data type conversions. Older environments or legacy systems might give you some unexpected results!

Anomalies and how to handle them

When you're grappling with casting, you might encounter a few obstacles. Here, we make our possible adversaries known.

Non-number VARCHAR wars

If the VARCHAR column contains more than just numbers, the CAST function might throw a tantrum. How do you handle such a fit? Use regular expressions to filter out those unruly characters:

SELECT CAST(REGEXP_REPLACE(varchar_col, '\\D', '') AS SIGNED) AS int_col FROM table;


// Don't let non-digits crash the party, kick them out with REGEXP_REPLACE!👟🎉

Overflow and truncation showdown

When converting VARCHAR to INT, there's a risk of the value exceeding the INT limit, leading to an overheating overflow or the silent assassin truncation. In MySQL, INT can only handle values between -2147483648 to 2147483647 for signed and 0 to 4294967295 for unsigned.

// Introducing... the ultimate showdown of limits! Will your values survive?

The performance Angle

When in the face of big data, casting needs to be optimized for performance. Here, we explore the corners of the optimization sphere.

Faster queries with indexing

Here's a performance tip: create an index on the VARCHAR column if you'll often cast it to an INT. Be cautious, though, adding index could slow down write operations.

ALTER TABLE table ADD INDEX (CAST(varchar_col AS SIGNED));


// Do you feel the need? The need for speed!

Doing it in batches

Undertaking large scale data migration or cleanup? Segregate operations into manageable batches. This approach will help to minimize the performance impact on your database server:

UPDATE table SET int_col = CAST(varchar_col AS SIGNED) WHERE id BETWEEN start_val AND end_val;


// Batch processing — saving DBAs from meltdown since 1970!