Cast for type conversion in SQL

Introduction

SQL CAST() function converts a value (of any type) into a specified datatype. This function converts an expression of one data type to another.

Syntax:

cast(expression as [datatype])

• [datatype] is a valid data type in relational database management System(RDBMS) you wish to convert an expression to
• Expression It is a valid expression where we want to convert a data type into the SQL.

Example

We will use students table with dummy data

• Students Table columns
  
  

• Students Table
  
  

Example 1

• we will use the CAST function to convert the marks column from type FLOAT to INTEGER.

select name, cast(marks as int)
from students;

or

select name, marks::int
from students;

• output

*Note you can use :: followed by data type instead of typing cast *

Example 2

Lets convert marks from a float to a char() of 3 characters. Try it out yourself before checking the answer

select name, cast(marks as char(3))
from students;

• Output
• In Example 2, we use the CAST function to convert the marks column from type FLOAT to CHAR (3). When we do it, we only hold the first 3 character. If there are more than three characters, everything after the first three characters is discarded.

Conclusion

CAST in SQL is a function to explicitly convert a value of one data type to another.