This chapter is from the book
This chapter is from the book
This chapter is from the book
Conversion Functions
All of the aforementioned functions pertain to specific ways to manipulate character, date/time, or numeric datatypes. We now want to address the need to convert data from one datatype to another, or to convert NULL values to something meaningful. The remainder of this chapter will cover two special functions that can be used in these situations.
The CAST function converts data from one datatype to another. The general format of the function is:
CAST(Expression AS DataType)
The format of this function is slightly different from other functions previously seen, as it uses the word AS to separate the two arguments, rather than a comma. Looking at the usage of the function, it turns out that the CAST function is unnecessary in most situations. Let’s take the situation where we want to execute this statement, where the Quantity column is defined as a character datatype:
SELECT 2 * Quantity FROM table
Your first impression might be that the statement would fail, due to the fact that Quantity is not defined as a numeric column. However, most SQL databases are smart enough to automatically convert the Quantity column to a numeric value so that it can be multiplied by 2.
Here’s an example where the CAST function becomes necessary. Let’s say we have dates stored in a column with a character datatype. We’d like to convert those dates to a true date/time column. This statement illustrates how the CAST function can handle that conversion:
SELECT
'2017-04-11' AS 'Original Date',
CAST('2017-04-11' AS DATETIME) AS 'Converted Date'
The output is:
Original Date |
Converted Date |
2017-04-11 |
2017-04-11 00:00:00 |
The Original Date column looks like a date, but it is really just character data. In contrast, the Converted Date column is a true date/time column, as evidenced by the time value now shown.
A second useful conversion function is one that converts NULL values to a meaningful value. In Microsoft SQL Server, the function is called ISNULL. As mentioned in Chapter 1, “Relational Databases and SQL,” NULL values are those for which there is an absence of data. A NULL value is not the same as a space or zero. Let’s say we have this table of products:
ProductID |
Description |
Weight |
1 |
Printer A |
NULL |
2 |
Printer B |
0 |
3 |
Monitor C |
2 |
4 |
Laptop D |
4 |
Notice that Printer A has a value of NULL in the Weight column. This indicates that a weight for this printer has not yet been provided. Let’s say we want to produce a list of all products. When this SELECT is issued:
SELECT Description, Weight FROM Products
It will show:
Description |
Weight |
Printer A |
NULL |
Printer B |
0 |
Monitor C |
2 |
Laptop D |
4 |
There’s nothing inaccurate about this display. However, users may prefer to see something such as “Unknown” rather than NULL for missing values. Here’s the solution:
SELECT Description, ISNULL(CAST(Weight AS VARCHAR),'Unknown') AS Weight FROM Products
The following data is displayed:
Description |
Weight |
Printer A |
Unknown |
Printer B |
0 |
Monitor C |
2 |
Laptop D |
4 |
Notice that the solution requires the use of both the ISNULL and CAST functions. The ISNULL function handles the display of the weight as “Unknown” when NULL values are encountered. Assuming the Weight column is defined as an integer, the CAST function is needed to convert the weight to a Varchar datatype, so both integer and character values can be displayed in a single column.