Explains how to transform returned data, or generate completely new columns in the result set, using built-in SQL functions
- [Instructor] SQL provides lots of functions to help you manipulate your data as you retrieve it. They're a bit like formulae in Microsoft Excel. Which functions you use depends which data type the field is that you want to run the function on. Some functions will only work on text fields, and others will only work on numeric or date fields. Some of them are a bit more flexible, one of those is length. Length counts the numbers of characters in a string, or indeed a number. So here's a query with a string function in it.
SELECT first_name which is a field, and then length of first_name. Most SQL functions take the same form. The function name followed by parentheses and then the field name inside. Now note that if you're using Microsoft Excel, it's len instead of length. So when we run this query, we see that PENELOPE has 8 characters, NICK has 4, and ED has 2. Using this function has created us a whole new column.
The first column here is a field name and the second column is a column that processes information from the first column, so we could say the second column here is derived, or synthetic. It's quite normal for an SQL query to return only synthetic data with no primary field data at all. Another common string function is CONCAT. CONCAT is short for concatenate which is a common function in any language, and it just adds strings together. If you concatenate the word "rain" and the word "bow," you get "rainbow," so let's try it in SQL.
SELECT CONCAT(first_name, space, last_name) FROM actor. Now the database returns us just the one column which contains elements processed from two fields. Now there are shortcuts for CONCAT in some versions of SQL, sadly not in MySQL. But if you're using Oracle or PostgreSQL you could write for example, first_name and then use the double pipe to indicate concatenation.
And if you're using Microsoft, instead of a double pipe it's a plus sign like so. But CONCAT worked across all of the functions. You can also combine string functions by placing one of them inside the other, so if we returned to our MySQL friendly version, there we have SELECT CONCAT, and then I'm going to copy this, put a comma at the end to indicate I want a new field, or a new column.
I'm gonna paste it like so, and place the whole thing inside a LENGTH function, and run that. So this returns us the full name plus the number of characters in the full name, and we can see PENELOPE GUINESS there has 16. ED CHASE has 8. If we wanted to find out which actor had the longest full name, we can order our results by length descending, and to do that we can say ORDER BY, then copy this like so.
And then say DESC for descending and run that. So now we can see the actor with the longest name is MICHELLE MCCONAUGHEY with 20 characters in their name. Now what if we wanted to order alphabetically instead, to see who's name was last in the alphabet. Then we could just say ORDER BY the concatenation of first and last_name, rather than order by the length. And there we've got ZERO CAGE, and if we change that to ASC it would show the results in the reverse order.
Each type of SQL has comprehensive documentation, available for free online by the way, so if you want to see a list of all the string functions available for your version, Google string functions with the database name, such as Oracle or MySQL. Now let's look at a couple more string functions. You might've noticed so far that all of the actor names are given in upper-case. Now you can use LOWER to turn them into lower-case like so.
As with most SQL functions, we've typed the function name and then the field name in brackets after it. So now we've got our names in lower-case. When you're dealing with names of course, it's more likely you'd want proper-case rather than lower-case, where the first letter of each word is capitalized, and the rest is lower-case. Now if you're using Oracle of PostgreSQL you can use initcap to achieve the same effect, but in MySQL and Microsoft there's no equivalent function, unless you write your own.
We can get 'round it in MySQL by using LEFT and RIGHT, which are both available in the other languages and useful obviously, for a lot more than just creating proper names. LEFT returns you part of a word from the left. LEFT starts at character one and counts to the right, so the left four characters of rainbow would be "rain." RIGHT starts at the final character of a string and counts to the left, so the right three characters of rainbow would be "bow." So what we do with LEFT is we say LEFT, open brackets, field name, comma, and then the number of characters we want and I'm going to go for 1, because I just want the first characters there from the actor name.
Where an SQL function takes more than one parameter, the parameters are usually separated by commas. Similarly we can run RIGHT (first_name,7) to get enelope, and we're gonna pop a filter in there, so that we're only returning PENELOPEs for now. So then what we do 'round the enelope section, we put LOWER like so, beginning to look like a proper name now, and then we can use CONCAT to join them together like so.
Now if we remove the WHERE clause we're going to see a drawback to this method. Each name of course has a different length, and this method only works where the total length of the name is 8. To make this statement work irrespective of length we need to use the function called LENGTH, and what we do there is say we want the first letter from the left, and it's already in upper-case so we don't need to put UPPER around this bit of statement, and then we have LOWER(RIGHT(first_name, and instead of a fixed-value of 7, we say we want the LENGTH of first_name minus 1.
And we're minusing the 1 because we don't want the P of PENELOPE, that's here. We just want enelope in this section. And you can see that that's worked for all of our actor names now, irrespective of their length.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries