Introduction:

Data transformation is a crucial aspect of database management and analysis. Often, data is stored in different formats, such as VARCHAR and INT. Converting VARCHAR to INT is a common requirement, especially when dealing with numerical data represented as text. In this blog, we'll explore multiple techniques to efficiently achieve this conversion in MySQL.

Method 1: Using CAST() Function

One straightforward way to convert VARCHAR to INT is by using the CAST() function in MySQL. This function allows you to explicitly specify the data type to which you want to convert the column. Here's an example:

/* Create a Table */
CREATE TABLE my_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    numeric_data VARCHAR(10)
);

/* Insert values to the table */
INSERT INTO my_table (numeric_data) VALUES
    ('123'),
    ('456'),
    ('789');

/* Select or get the records from the table including the VARCHAR to INT conversion */    
SELECT
    id,
    CAST(numeric_data AS SIGNED) AS converted_data
FROM my_table;

Output:

In this example, we create a sample table my_table with a VARCHAR column numeric_data. We then use the CAST() function to convert the numeric_data column to the INT data type.

Method 2: Using CONVERT() Function

MySQL also provides the CONVERT() function, which can be used to convert VARCHAR to INT. The syntax is similar to the CAST() function:

SELECT
    id,
    CONVERT(numeric_data, SIGNED INTEGER) AS converted_data
FROM my_table;

Output:

The CONVERT() function performs the same task as CAST() and is an alternative in MySQL for compatibility with other database systems.

Method 3: Removing Non-Numeric Characters with REGEXP_REPLACE()

When dealing with VARCHAR columns containing non-numeric characters, using CAST() or CONVERT() directly may result in undesired output. To handle such cases, we can use the REGEXP_REPLACE() function along with CAST() or CONVERT() to remove non-numeric characters before conversion:

SELECT
    id,
    CAST(REGEXP_REPLACE(numeric_data, '[^0-9]', '') AS SIGNED) AS converted_data
FROM my_table;

Output:

The REGEXP_REPLACE() function removes all non-numeric characters from the numeric_data column using the regular expression pattern [^0-9], which matches any character that is not a digit (0-9).

Method 4: Handling NULL Values

In some cases, the VARCHAR column may contain NULL values. When converting to INT, we might want to preserve NULL rather than converting it to 0. To achieve this, we can use the IFNULL() function along with the previous methods:

INSERT INTO my_table (numeric_data) VALUES
    ('123'),
    ('456'),
    (NULL);
    
SELECT
    id,
    IFNULL(CAST(REGEXP_REPLACE(numeric_data, '[^0-9]', '') AS SIGNED), NULL) AS converted_data
FROM my_table;

Output:

The IFNULL() function checks if the result of the conversion is NULL and replaces it with NULL, ensuring that NULL values are preserved.

Conclusion:

In this blog, we explored multiple efficient techniques for achieving this conversion. We started with using CAST() and CONVERT() functions, followed by handling non-numeric characters using REGEXP_REPLACE(), and finally, preserving NULL values with the IFNULL() function.