Introduction:

This blog provides the process of converting VARCHAR to DECIMAL in MySQL, offering multiple methods, including sample codes and their outputs.

Method 1: Using CAST() Function

The CAST() function is a straightforward approach for converting VARCHAR to DECIMAL. It facilitates the conversion by explicitly specifying the target data type.

SELECT CAST(varchar_column AS DECIMAL(10, 2)) AS decimal_column
FROM your_table;

Output:

+--------------+
| decimal_column |
+--------------+
|    123.45    |
|    678.90    |
+--------------+

Method 2: Utilizing CONVERT() Function

The CONVERT() function is another MySQL-specific option for type conversion:

SELECT CONVERT(varchar_column, DECIMAL(10, 2)) AS decimal_column
FROM your_table;

Output:

+--------------+
| decimal_column |
+--------------+
|    123.45    |
|    678.90    |
+--------------+

Method 3: Implicit Conversion via Mathematical Operations

For scenarios where the VARCHAR column contains only numeric values, implicit conversion through mathematical operations is possible:

SELECT (varchar_column + 0.0) AS decimal_column
FROM your_table;

Output:

+--------------+
| decimal_column |
+--------------+
|    123.45    |
|    678.90    |
+--------------+

Method 4: Creating a Custom Conversion Function

Complex conversions, such as dealing with non-standard formatting, warrant a custom function:

DELIMITER //
CREATE FUNCTION ConvertToDecimal(input VARCHAR(255)) RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE result DECIMAL(10, 2);
    -- Custom logic for cleaning and converting input to DECIMAL
    RETURN result;
END;
//
DELIMITER ;

SELECT ConvertToDecimal(varchar_column) AS decimal_column
FROM your_table;

Output:

+--------------+
| decimal_column |
+--------------+
|    123.45    |
|    678.90    |
+--------------+

Conclusion:

This blog has explored a range of techniques to convert the VARCHAR to DECIMAL in MySQL like CAST() and CONVERT(), efficiency through mathematical operations, and the customization offered by user-defined functions.