SQL CAST

The SQL CAST function is used to convert one data type into another. It is particularly useful when dealing with data that needs to be presented or calculated in a different format. The CAST function is commonly used in SELECT statements, especially for type conversions involving numbers, dates, and strings.

In this tutorial, we will explore the SQL CAST function, its syntax, and how to use it with practical examples.

Syntax of SQL CAST Function

The basic syntax of the SQL CAST function is:

</>
Copy
CAST(expression AS target_data_type)

Explanation:

  • expression: The value or column that needs to be converted.
  • target_data_type: The data type to which the expression should be converted (e.g., INT, VARCHAR, DECIMAL, DATE).

Step-by-Step Examples Using SQL CAST

1. Converting String to Integer

Suppose we have a table employees that stores employee details, including salaries as text values. We want to convert the salary from VARCHAR to INT for calculations.

In SQL Server:

</>
Copy
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    salary VARCHAR(10)
);

Insert some sample data:

</>
Copy
INSERT INTO employees (name, salary)
VALUES 
('Arjun', '50000'),
('Ram', '60000'),
('Priya', '70000');

Now, we will convert the salary column from VARCHAR to INT using CAST:

</>
Copy
SELECT name, salary, CAST(salary AS INT) AS salary_int
FROM employees;

Explanation:

  • CAST(salary AS INT) converts the text-based salary into an integer.
  • This allows us to perform mathematical operations such as aggregations and comparisons.

2. Converting Integer to Decimal

Suppose we have an orders table where the total price is stored as an integer, but we need to convert it to a decimal format for accurate representation.

In MySQL,

</>
Copy
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(50),
    total_price INT
);

Insert some sample data:

</>
Copy
INSERT INTO orders (customer_name, total_price)
VALUES 
('Arjun', 1500),
('Ram', 2999),
('Priya', 3499);

Now, we will convert the total_price column from INT to DECIMAL(10,2) using CAST:

</>
Copy
SELECT customer_name, total_price, 
       CAST(total_price AS DECIMAL(10,2)) AS price_decimal
FROM orders;

Explanation:

  • CAST(total_price AS DECIMAL(10,2)) converts the integer price into a decimal format with two decimal places.
  • This ensures proper financial calculations and accurate monetary representation.

Conclusion

The SQL CAST function is used for converting data types within queries. In this tutorial, we covered:

  1. The CAST function’s syntax and how it works.
  2. Converting VARCHAR to INT for calculations.
  3. Converting INT to DECIMAL for accurate numerical representation.

Understanding the CAST function allows for better handling of data type conversions, leading to improved database operations.