Sai A
Updated date Aug 24, 2023
This blog provides various methods for converting VARCHAR to DECIMAL. Learn the methods like CAST(), CONVERT(), mathematical operations, and even custom functions.
• 3.4k
• 0
• 0

## 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.