Cast Various Types into Decimal Types in SQL

February 22, 2024 | by Arround The Web | No comments

Cast Various Types into Decimal Types in SQL

What can we say about type casting or type conversion that hasn’t been said? Type conversion is one of the most fundamental and common practices for any developers in SQL or any other programming language.

In SQL, we have access to the conveniently named cast() function which allows us to cast a value from one data type into another. A common use of the cast function is when you need to change the data type of a given column or expression and then perform specific operations or comparison to the resulting type.

In this guide, we will focus on the cast() function in SQL and learn how we can use it to convert various types into a decimal type.

It is good to keep in mind that the implementation of the cast() function may vary depending on the target database engine. Therefore, consider the documentation for your database to learn more.

Function Syntax:

The syntax of the cast() function is quite straightforward and very intuitive. We can express the syntax as follows:

CAST(expression AS data_type)

The function accepts two main parameters:

Expression – This refers to the value, expression, or table column that we wish to convert into another type.
Data_type – This specifies the target data type into which we wish to cast the input expression.

With that out of the way, let us look at some example usage of the cast() function.

Example 1: Cast an Int to Decimal

One of the most common type of casting operations is converting an integer into a decimal value. For example, suppose we have a column of integer values and we wish to convert it into a decimal type for more precise calculations. We can use the cast() function as follows:

SELECT CAST(value AS DECIMAL(10, 2)) AS decimal_values

FROM sample;

In this case, we have a column called “values” that stores the integer values. Using the “cast” function, we quickly convert all the values into decimal as shown in the following output:

decimal_values|
--------------+
100.00|
200.00|
300.00|

This provides a more precise calculation of the values.

NOTE: In the given function, we specify the target data type which is a decimal with a total of 10 digits, and two of them are reserved for decimal places.

Example 2: Cast the String to Decimal

Another case is converting a string into decimal values. For example, we have a column that contains numerical values but they are stored as strings. This is a common occurrence especially when importing a data from external sources.

We can use the “cast” function to convert the values from the string into decimals as shown in the following example:

SELECT CAST('49.99' AS DECIMAL(8, 2)) AS dec;

This should convert the input string into the decimal representation as follows:

dec |
-------------+
49.99|

There you have it!

Example 3: Dealing with NULL values

In the real world, you are bound to come across instances where the rows in your column contains NULL values.

Although this might not seem like a problem at first, when you need to convert the values into other type, the cast() function will not take it nicely to NULL values.

To account for that, it is good to handle the NULL values gracefully. We can do this using the COALESCE function to input a default value where there is NULL.

Take for example the following query:

SELECT CAST(COALESCE(column_name 0) AS DECIMAL(10, 2)) AS result

FROM table_name;

The previous syntax demonstrates how to use the coalesce() function to handle the NULL values with the cast() function.

Conclusion

In this guide, we explored the “cast” function in SQL to learn how we can use it to convert various types into decimal types. We covered the function syntax and example usage of the function.

Source: linuxhint.com