Customizing output
1. Customizing output
In this video, we'll talk about customizing output like uppercasing characters or selecting the first characters of each cell in a column.2. Functions
To customize output, we often have to use functions. Functions can be used for calculation, formatting, manipulation, and conversion between data types.3. Functions and data types
Do you remember this slide from chapter 2? AVG, SUM, MIN, and COUNT are functions you've used before. Certain functions work with only specific data types. Data types define what type of data a column can contain. There are three main categories: numeric, character, and date.4. Types of functions
Therefore, it's useful to split functions into categories. Character functions accept character values and can output characters and numeric, and date values. Number functions accept number values and returns number values. The last three categories will be covered in the next two videos. Let's take a look at examples of the first two categories.5. Case manipulation: upper case
UPPER() is a function that converts all characters into upper case. For example, you could use this function to make sure state acronyms and letters in any postal codes are properly capitalized. We put the columns of interest within the function parentheses.6. Case manipulation: lowercase
Similarly, there's also the LOWER() function for lower case. In this example, we're making sure all emails are in lowercase format.7. Getting a substring
SUBSTR() is another character function and it returns a substring of a string. It has three inputs: the column of interest, the starting position of the substring, and the length of the substring. Let's look at an example. We can get the phone numbers of all customers. Imagine we want to get the country code of these phone numbers without the plus sign.8. Getting a substring
We can get a substring of the phone number to do this. We skip the plus sign, which is the first character, and our starting point is the second character, 5 for the first row. In our case, the substring would be two characters in length. And here we have the desired output!9. Nested functions
You can nest functions within each other. For example, we previously learned about the CONCAT function which lets you combine two strings. Imagine your manager asks you to generate unique and readable usernames for customers. You decide that a username contains the first 5 letters of their last name and their customer id. We can write a SQL command to automatically generate this. To get the first five letters of customers last names we use SUBSTR(). Then we can encapsulate within a CONCAT() function to append the customerid. Our output looks like this!10. Other useful character functions
There are many available functions out there. Here are some other useful functions! LENGTH() gives you the length of a string, and REPLACE() lets you, well, replace letters within strings.11. Rounding
Let's look at a numeric function. ROUND() is used to round values to a specific decimal point. In this example, we round the total of invoices to one decimal and to a whole number.12. Truncating
Another function is TRUNC() which truncates decimal points rather than rounding. Using the same example as the last slide, we can compare truncation and rounding. Truncation cuts off values of a number at a specific decimal point, so we see differences with rounding.13. Modulo
The last function we will cover is MOD(), which returns the modulo, in other words the remainder of the division. For example if we divide 14 by 4, the remainder is 2, because the closest four-multiple is 12.14. Modulo
It's useful for checking whether a number is even or odd. All we have to do is divide by 2 and see if there is a remainder or not.15. Modulo
We can check whether we have an even amount of employees with MOD() for example.16. Let's practice!
Let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.