Name
CAST
Synopsis
The CAST command explicitly converts an expression of one datatype to another. All vendors provide the ANSI SQL2003 syntax for CAST.
SQL2003 Syntax and Description
CAST(expression
ASdata_type
[(length)])
The CAST function converts any expression, such as a column value or variable, into another defined datatype. The length of the datatype may be optionally supplied for those datatypes (such as CHAR or VARCHAR) that support lengths.
Tip
Be aware that some conversions, such as DECIMAL values to INTEGER, will result in rounding operations. Also, some conversion operations may result in an error if the new datatype does not have sufficient space to display the converted value.
Examples
This example retrieves the year-to-date sales as a CHAR and concatenates it with a literal string and a portion of the title of the book. It converts ytd_sales to CHAR(5), plus it shortens the length of the title to make the results more readable.
SELECT CAST(ytd_sales AS CHAR(5)) + ' Copies sold of ' + CAST(title AS VARCHAR(30)) FROM titles WHERE ytd_sales IS NOT NULL AND ytd_sales > 10000 ORDER BY ytd_sales DESC
The results are:
--------------------------------------------------- 22246 Copies sold of The Gourmet Microwave 18722 Copies sold of You Can Combat Computer Stress 15096 Copies sold of Fifty Years in Buckingham Pala
Get SQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.