How CAST works in SQL? Best CAST examples

How CAST works in SQL? Best CAST examples

Understanding data types in SQL is crucial, and sometimes, we need to convert data from one type to another. That’s where the CAST function comes in. It allows us to change a value’s data type explicitly, making it easier to manipulate and analyze data. In this article, I’ll explain how CAST works in SQL, share some best CAST examples, and highlight its practical applications.

What is CAST in SQL?

The CAST function in SQL is used to convert an expression from one data type to another. It follows ANSI SQL standards and works across most relational database management systems (RDBMS) such as MySQL, SQL Server, PostgreSQL, and Oracle.

The basic syntax of CAST looks like this:


CAST(expression AS target_data_type)

Here’s what’s happening:

  • expression – The value or column you want to convert.
  • target_data_type – The data type you want to convert to.

Why Use CAST?

The CAST function is useful for several reasons:

  1. Ensure Data Consistency: When working with multiple data types in queries.
  2. Data Formatting: Converting numbers to text or vice versa for better representation.
  3. Mathematical Operations: When arithmetic operations require specific numeric types.
  4. Database Compatibility: Some functions or conditions need specific data types to work correctly.

Best CAST Examples in SQL

1. Converting String to Integer

Let’s say we store numbers as text in our database but need to perform calculations:


SELECT CAST('12345' AS INT) AS ConvertedNumber;

Output:

ConvertedNumber
12345

2. Convert Integer to String

Sometimes, we need to change an integer into a string for concatenation.


SELECT CAST(100 AS VARCHAR(10)) AS ConvertedString;

Output:

ConvertedString
100

3. Converting Date to String

Changing a date type into a more readable string format:


SELECT CAST(GETDATE() AS VARCHAR(20)) AS DateAsString;

4. Converting Decimal to Integer

If you need to round down a decimal value to an integer:


SELECT CAST(125.89 AS INT) AS RoundedNumber;

Output:

RoundedNumber
125

5. Using CAST in Arithmetic Operations

Sometimes, division results in an integer, losing precision. We can use CAST to convert an operand into a decimal:


SELECT CAST(10 AS DECIMAL(5,2)) / 3 AS DecimalResult;

Output:

DecimalResult
3.33

Differences Between CAST and CONVERT

In SQL Server, there’s another function called CONVERT, which serves a similar purpose but with more formatting options. Here’s a quick comparison:

Feature CAST CONVERT
Standard SQL? Yes No (SQL Server only)
Formatting Options No Yes
Usage Cross-platform conversions More control over date and number formats

Common Errors When Using CAST

While CAST is useful, it can run into issues if conversions fail. Here are some common problems:

  • Conversion Failed Errors: Trying to convert ‘abc’ to an INT will cause an error.
  • Data Loss: Converting from a higher precision data type to a lower one (e.g., DECIMAL to INT).
  • String Truncation: Converting a long string to a shorter VARCHAR may cut off data.

Final Thoughts

The CAST function in SQL is an essential tool for handling data type conversions. Whether you’re dealing with numbers, strings, or dates, knowing how to use CAST properly will help you write better SQL queries. While CONVERT offers additional formatting in SQL Server, CAST remains a cross-platform solution that works in almost any database.

 

Other interesting article:

How GROUP_CONCAT works in SQL? Best GROUP_CONCAT examples