In many cases, a database will automatically convert one data type into another when needed. On the other hand, there are instances when that is not the case, or when you want to explicitly specify what data type to change into. In these cases, you can use the CAST function. 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.
Let's go through some examples to see how the CAST function is used. Let's assume we have the following table:
Table Student_Score
| StudentID | integer |
| First_Name | char(20) |
| Score | float |
and 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
SQL:
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, we assume that the numbers after the decimal point are always truncated.
Example 2
SQL:
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 3 characters. So, if there are more than 3 characters, everything after the first 3 characters is discarded.