1. Using conversion functions
Nice job on those exercises! In this video, we'll be talking about conversion functions.
2. Data types
We briefly covered data types in chapter 2. Data types define what type of data a column can contain.
As you can see in this table that we've seen before, certain functions work with only specific data types. Often times, we want to convert between data types, whether it's so that we can use specific functions or to ensure data integrity in our database.
This is exactly what conversion functions are for!
3. Conversion functions
There are two types of data type conversion: implicit and explicit conversion.
Implicit data type conversion means that Oracle SQL will automatically convert datatypes if a statement contains conflicting datatypes.
Conversion can also be done explicitly by the user with conversion functions. This is called explicit data type conversion.
Let's take a closer look at each of these.
4. Implicit data type conversion
Implicit conversion occurs automatically in circumstances where SQL will determine on its own that a data type conversion is required but not specified.
For example, let's say you want to concatenate a numeric value with a text string. Note that the second value in the SELECT list, milliseconds, is numeric yet is included in a text function for performing string concatenation. SQL will recognize the intent and will perform the character conversion automatically.
5. Explicit data type conversion
But what if we want to be explicit about the data type conversions? SQL provides three functions to convert a value from one data type to another: TO_CHAR(), TO_NUMBER(), and TO_DATE().
On this diagram you can see how they can be used to convert between the different data types: number, character, and date. Note that you can't convert between numbers and dates directly, but need to convert to a character value first.
6. Converting to character data
When working with number values such as character strings, you should convert those numbers to the character data type using the TO_CHAR() function.
In the example, the UnitPrice column, which has the number data type, is converted to a character string and formatted with a dollar sign and a 2 decimal places.
Note that we added the dollar sign and specified the number of decimal places. There are more format elements that you can use with the TO_CHAR() function to display a number value as a character. You can pause the video and experiment with them if you want.
7. Converting to character data
If you want to convert date values to a character data, you can also use the TO_CHAR() function.
Let's look at an example. The BirthDate, which has the date data type, is converted to a character string. You can also specify a format for the date to appear in. Let's put the day first, then a three-letter representation of the month, and then the year, all separated by dashes.
Once again, there are more format elements that you can use with the TO_CHAR() function to display a date value as a character. Take a look and try them out on your own time.
8. Converting to numeric data
You may want to convert a character string to a number. To accomplish this task, you can use the TO_NUMBER() function.
In this example, the starting value is a string, 15000 point 75 dollars. This isn't a numeric data type but a character string containing a dollar sign and a comma.
The same format elements from before can be used. These help make it clear where the significant numeric data can be found.
9. Converting to date data
You may want to convert a character string to a date. To accomplish this task, you can use the TO_DATE() function.
In this example, the starting value is 2016-01-31. This isn't a date data type but a character string containing in a certain format. You need to tell Oracle SQL what that format is so it can interpret the date correctly. Here it's, year first, then month, then the day.
The same format elements from before can be used. These help make it clear where the significant numeric data can be found.
10. Which data type conversion should you use?
Always use explicit conversion. Besides being much easier to read and maintain, explicit conversion ensures that if a default format is changed somewhere, your code will continue to work.
11. Let's practice!
Alright, let's get to some exercises.