Builds knowledge of built-in SQL string functions, including the return of partial strings and cleaning of returned data
- [Instructor] We've used left and right to return parts of a string from the beginning or end, respectively. But, what if you wanted to return a partial string from the middle of a string? Here, we would use substring. So, let's recreate our actor name example using that. Substring takes two parameters. Or in fact, it takes three parameters. It takes the field name. And then, two numerical values.
It takes the starting character, which is one and not zero. For the first letter of the name. And then, it takes the number of the characters that it should look for. Which for 'P' in Penelope, is just one. So, if we run that, we see we have the first letter's returned to us. Let's have another look at that function. If we were to try 1,2 you see we get two characters from each of them there. We've got PE and NI and ED.
And, if we tried 2,1 that would return us the second character in the string. So, let's pop that back to 1,1 as it was. Now, for our second column, going to try first_name ,2 and you see that's returned us all the rest of the name. Now, let's look at that a bit more closely. It should look a bit odd to you, because I said that substring takes two parameters on top of the field name.
And yet here, we've just supplied one. In fact, substring is quite an advanced function, and it can accept commands in a number of ways. We've told it that we want a partial string from the first_name field. We've told it to begin at character 2. And, the lack of a third parameter, which is the length, tells phpMyAdmin we want the rest of the word, however long it may be. So, substring has an advantage over left and right. In that, you don't need to couple it with length.
We could also use a negative number here. So, we could put -4 and that would tell the software that we want to start counting from the end of the string. So you can see there, we've returned LOPE, at the end of Penelope. Now, back to our original statement, we need to concatenate these two together. To get a proper cased Penelope. So, we'll say CONCAT and, of course, you'll need to put LOWER around that.
And, that returned us the proper name, as before. Apart from grace, which you might notice here. And, we're going to look at that in a second. That's an actor name that I modified earlier. The final two string functions we're going to look at here are trim and locate. Used in its simple form, the trim function removes spaces from a word, if they're there. And, returns the word unmodified if they aren't there. Now, as we just saw, there's an active in the table called grace. But, if we run the following command: SELECT * FROM 'actor' WHERE first_name ='grace' We don't find her.
It's because, I've added some spaces before and after the string grace. Spaces before a string are called leading spaces, by the way. And those after it, are called trailing. The word grace, or the string grace, now has both kinds of space. And, that's why it's not being found. We'd have to add the correct number of spaces here, to match what's in the database, in order to find a match. But, we don't want to do that, we want to use the trim function. So, what we can do is say: WHERE TRIM(first_name)='grace' And there, we find our row.
In Microsoft, there are LTRIM and RTRIM functions. Instead of the TRIM function. And PostgresSQL also offers LTRIM and RTRIM. Which trim from the left and the right, respectively. Trim can be really useful for removing unwanted spaces when your data quality isn't great. But actually, it's much more advanced than that. If we click on the film text table here, and have a look a the data. If you look at the description column, you can see that every description starts with a capitol A and a space.
Even when the second word begins with a vowel. And so, the description the description really ought to start with AN, instead of A. In fact, I checked and every single row begins this way. So, what we can do is use trim to remove the A and the space. And, what we can do is say, SELECT description, for comparison purposes. And then, as a second column we're going to say, SELECT TRIM LEADING 'A space FROM description FROM description And, let's run that.
So, there's before and after. Leading, tells the database only to look for occurrences at the beginning of the text. And trailing, would tell the database to look for occurrences at the end. We use single quotes around the thing we want to remove. Because, we are supplying text. You can also, as well as leading or trailing here, you can also use the word both to look for characters, at both, the beginning and the end of the string.
And, you can specify any set of characters you want to be removed. You can use trim on strings, numbers, and dates. So, if your database stores some unique ID, with a bunch of leading zeroes for example, you can remove all of this to return the actual number. The final string function we're going to look at here, is locate. Which gives you the location, or the position, of one string inside another. In MySQL, locate will return you a zero, if it can't find the search string.
So, here we can go, SELECT LOCATE and then we supply the string we want. Followed by the field name. And, that's the first time we've seen a function work like that. Until now, we've supplied the field name first. So, LOCATE 'lope' within first_name FROM actor. And, MySQL has returned us a 5 in the first row. Which contains penelope.
In fact, let me just return the field itself, which will help. So, it's returned us a 5, because it's found lope there. In this first row, and then zeroes on all the others. Because, it can't find lope within nick, unsurprisingly. Unfortunately, the different SQL versions have all used different terms here. So, in MySQL and in Oracle, you can use LOCATE. In PostgrSQL it's POSITION. And, in Microsoft it's CHARINDEX.
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