Convert Strings to Decimals in MySQL

In MySQL, you can converting strings to decimals using the CAST() or CONVERT() functions. These functions allow you to change a string to a decimal type with specified precision.

Using CAST() to Convert Strings to Decimals

The CAST() function converts one data type to another. To convert a string to a decimal, use CAST() with the DECIMAL data type, specifying the desired precision (total digits) and scale (digits after the decimal point).

</>
Copy
CAST(string_value AS DECIMAL(precision, scale))

Examples: Converting Strings to Decimals in MySQL

Let’s go through a few examples to demonstrate how to convert strings to decimals using CAST() and CONVERT().

1. Basic String to Decimal Conversion Using CAST()

To convert a string containing a numeric value, such as '123.45', to a decimal with two decimal places:

</>
Copy
SELECT CAST('123.45' AS DECIMAL(5, 2)) AS decimal_value;

This query converts '123.45' to a decimal value of 123.45 with two decimal places. The precision (5) and scale (2) ensure it is stored with up to five total digits, including two digits after the decimal point.

Convert Strings to Decimals in MySQL - Example: 1

2. Converting Strings with Varying Decimal Places

To convert a string with a numeric value to a decimal with different precision and scale, for example, '4567.891' to a decimal with three decimal places:

</>
Copy
SELECT CAST('4567.891' AS DECIMAL(7, 3)) AS decimal_value;

This query converts '4567.891' to a decimal with three decimal places. The result is 4567.891 with a precision of 7, allowing for up to seven digits in total, including three after the decimal point.

Convert Strings to Decimals in MySQL - Example: 2

3. Using CONVERT() to Convert Strings to Decimals

The CONVERT() function is another way to convert strings to decimals. It functions similarly to CAST() and requires the target data type and precision.

</>
Copy
SELECT CONVERT('789.1234', DECIMAL(6, 2)) AS decimal_value;

This query converts '789.1234' to a decimal with two decimal places, resulting in 789.12. Here, the precision is set to 6 and the scale to 2, so only two decimal places are retained.

Convert Strings to Decimals in MySQL - Example: precision is set to 6 and the scale to 2, so only two decimal places are retained

By using CAST() and CONVERT() functions, you can convert strings to decimals in MySQL, tailoring the precision and scale as needed for calculations and data formatting.