In MySQL and SQL Server, the CONVERT function is very similar to the CAST function in that they both change the value from one data type to another. In this scenario, the syntax of the CONVERT function is as follows:
CONVERT (expression, [data type])
where [data type] is a valid data type in the RDBMS you are working with.
Let's go through an example to see how the CONVERT 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
SQL:
SELECT First_Name, CONVERT(Score, Integer) Int_Score FROM Student_Score
Result:
First_Name | Int_Score |
Jenny | 85 |
Bob | 92 |
Alice | 90 |
James | 120 |
In Oracle, the CONVERT function is used differently. It converts a string from one character set to another. Its syntax is:
CONVERT (string, [new character set], [original character set])