SQL CAST Function

SQL > SQL String Functions > CAST Function

The CAST function in SQL converts data from one data type to another. For example, we can use the CAST function to convert numeric data into character string data.

Syntax

The syntax of the CAST function is as follows:

CAST (expression AS [data type])

where [data type] is a valid data type in the RDBMS you are working with.

Examples

We use the following table for our examples.

Table Student_Score

Column Name	Data Type
StudentID	integer
First_Name	char(20)
Score	float

This table contains the following rows:

Table Student_Score

StudentID	First_Name	Score
1	Jenny	85.2
2	Bob	92.5
3	Alice	90
4	James	120.1

Example 1

SELECT First_Name, CAST(Score AS Integer) Int_Score FROM Student_Score;

Result:

First_Name	Int_Score
Jenny	85
Bob	92
Alice	90
James	120

In Example 1, we use the CAST function to convert the Score column from type FLOAT to INTEGER. When we do this, different RDMBS have different rules on how to handle the numbers after the decimal point. In the above example, the numbers after the decimal point are always truncated.

Example 2

SELECT First_Name, CAST(Score AS char(3)) Char_Score FROM Student_Score;

Result:

First_Name	Char_Score
Jenny	85.
Bob	92.
Alice	90
James	120

In Example 2, we use the CAST function to convert the SCORE column from type FLOAT to CHAR(3). When we do this, we only take the first three characters. So, if there are more than three characters, everything after the first three characters is discarded.

Next: SQL CONVERT Function

This page was last updated on October 12, 2024.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.