Introduction
The CAST SQL function is generally used to convert the expression from one data type to another data type. Sometimes we need to perform conversion from one data type to another in SQL. Do you know how we can achieve this? If not, don't worry; we will discuss how to achieve this using cast SQL.
In this article, we will discuss about the CAST SQL function, its syntax, properties, applications, and drawbacks. We will understand the concept of CAST SQL with an example.
CAST SQL
In Sql, the cast function is used for converting a value of one data type to another. We can convert the data type by specifying the data type we want it to convert to. This function accepts two parameters, i.e., the value we want to convert and the desired data type. The cast() function returns the value of the converted data type, but if it fails to return the value, it returns an error.
Before studying cast SQL, one must know the data types and their uses. In SQL, multiple data types exist, such as ‘INT,’ ‘VARCHAR,’ ‘FLOAT,’ ‘DATETIME,’ ‘DATE,’ and many more. One must know about the functions of each and their characteristics.
Apart from these, we must know about SQL’s syntax and should know how to write basic queries.
Syntax
Below is the syntax of CAST SQL.
CAST( expression AS [data type] [length] )
-
The [data type] is a valid data type in RDBMS.
-
The expression refers to the value or expression that we wish to convert to a specific data type.
-
[data type] is the type of data we want.
- [length], an optional parameter, sets the desired data type's length. Its default value is 30.
Example 1:
First, we create a table named as “students” containing students data like ‘student_id’, ‘name’ and ‘marks’.
Code
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
marks INT
);
INSERT INTO students (student_id, first_name, marks)
VALUES
(1, 'Ninja1', 90.8),
(2, 'Ninja2', 85.56),
(3, 'Ninja3', 78),
(4, 'Ninja4', 92.796),
(5, 'Ninja5', 88.01);
SELECT * FROM students;Output
Explanation
In this, we have created a table named students, which contains information about students like ‘student_id’, ‘first_name’, and ‘marks’ obtained.
Now, let's perform cast function to above table.
Code
SELECT
first_name,
CAST(marks AS INT) AS converted_integer_marks
FROM
students;Output
Explanation
In this code, we have selected the first_name from the “students” table and cast the “marks” column as an integer. The "AS" keyword is used to give a label to the column that is created by casting (converting) the "marks" column to a different type (Integer).
Example 2:
Let's understand CAST() function through another example. Consider the same table that we have consider above.
Code
SELECT
first_name
,
CAST(marks AS CHAR(6)) Char_marks
FROM
students;Output
Explanation
This code will show the "first_name" and "Char_Score" columns for each row in the "students" table. The "Char_Score" column will show the "marks" values converted to a fixed-length character of 6.
Also see, SQL EXCEPT
237+ registered 
