There may be times when you need to convert values from one data type to another, for example, from a numeric value to a character string. To convert a value to a different type, use the CAST specification.
Another possible use for a cast specification is to truncate a very long character string. In the EMP_RESUME table the column RESUME is CLOB(5K). You may want to display only the first 370 characters containing the personal information of the applicant. To display the first 370 characters of the ASCII format of the resumes from the table EMP_RESUME, issue the following query:
SELECT EMPNO, CAST(RESUME AS VARCHAR(370))
FROM EMP_RESUME
WHERE RESUME_FORMAT = 'ascii'
A warning is issued informing you that values longer than 370 characters are truncated.
You can cast NULL values to other data types that are more convenient for manipulation in a query. Common Table Expressions is an example of using casting for this purpose.