Hello everybody and in this lesson we're going to be taking a look at string functions. Now, string functions are built-in functions within my SQL that will help us use strings and work with strings differently. Now we're going to look at a ton of different ones. They all have different use cases, but I'll try to walk through some of those as we go along, but we'll look at a lot of different string functions in this lesson. We'll start off with one that's really simple. This one is called length. So, if we select and then we say length, and let's say we put in And I don't know why it's popping up like that. Let's say we put in something like sky or a skyfall or really anything. If we run this, it's going to give us the length of how long this string is. So, if I come down here, and we say select everything from employee_demographics and let's add a semicolon here. Let's run this one right down. Now, what we can do is we can look at how long each person's name is. So, what we can do is just take the first name, but then we'll also do the length of the first_name. So, if we run this, now we get Leslie, Tom, Jerry, Donna and it gives us the length of their name. If we wanted to, we could even order by this. So, we could do order by and we could just do two for now. And we can order by the length from the shortest name all the way to the longest name. Now, one use case that I've used length for in my actual job was when I was working with phone numbers. I wanted to make sure that they were exactly 10 characters long, otherwise something went wrong somewhere in the data cleaning process. So, I would go and look at the length and I would make sure they're all 10. If any were above 10, I would go and specifically look at those and try to clean those and fix those up. Now, let's go onto the next one. And these next ones are pretty simple ones. At least I think they're fairly simple. We're going to look at upper first, and it's doing the same thing as the other one. We'll do upper and let's say we're going to do sky. If we select upper sky, it's going to give us an all uppercase. Or we can copy this and we can do lower. So now, let me add semicolons, otherwise it's going to drive me crazy. Let's try this lower now. It's going to do all lower, even if I make it all capital. So, if I say all capital sky, it's going to make it all lower. So, if we come back up, let's copy this, and instead of doing the length, now we'll do upper. So, let's go ahead and select this. So, we have Leslie and then we have the upper first name. So, upper all case Leslie. Now, this is actually really good. This is really helpful, especially with standardization is what I found a great use case for it, because sometimes it'll be all capital Tom, and sometimes they'll put it in as t lowercase o m. And just making them all uppercase or all lowercase can help correct those really simple standardization issues within a single column. The next one that we're going to look at is trim. Now, there's multiple trims. We have trim, left trim, and right trim. Trim is basically going to take the white space on the front or the end and get rid of it, which is really, really helpful. So, what we're going to do is we're going to come right here. Let's say select, and we'll start off with trim. And let me add a semicolon every time. So then we'll do trim and for our actual string, we'll do something a little bit odd. We'll do some spaces and then we'll do sky and then we'll add some spaces. Now, let's run this and add our semicolon. That's going to be the end of me in this lesson. Just adding semicolons. Now, it fixes it completely. Now, what if we don't add sky at all? We'll just keep it like this. Well, you can see there's spaces before and there's spaces after, but that's what trim does. Trim gets rid of the leading and the trailing white spaces. Now, if we come up here and we just do the left trim, it's only going to remove from the left-hand side. So, we're only getting rid of the left-hand side white spaces. This right-hand side, as you can see is really long, it's still there. And if we do R trim, we go ahead and run this one, it gets rid of the white space on this side, but it doesn't get rid of the left space on this side. Now, let's keep going. We have a lot to cover still. We're going to move on to what I think is probably my most favorite string function, if I'm allowed to have a favorite string function, and that's substring. But I'm going to kind of work us into substring a little bit by looking at two smaller functions, which is left and right. So, let's select everything and we'll do that from the employee demographics again. Let's add our semicolon. Now, I'm going to run this. Now, I want to get the first name, and I'm going to do left of the first_name, just like this. Now, when you're using this, it's actually going to be an error. Let's see if I highlight over this if it'll tell me what the error is. It says, "The parenthesis is not a valid position. They're expecting something else." And basically what they're telling us is that this is not how it should be written. We're looking for a different value. That different value is actually a number. We're going to do comma and let's do four. That's what it was looking for. It didn't want this at the end. It needed this comma four. And what we're actually specifying is how many characters from the left-hand side do we want to select? So, we're selecting the first name and we're going from the left four characters. Let's go and run this. And so, we have Leslie, Tom, April, all the way down. You can see that there's only four characters in each one. So, someone like Chris, the S is no longer going to be there because we're only looking at the first four characters. Now we can do the exact same thing, and let's actually copy this down here. So, we'll come, we'll go like this. Try to make this a little more professional. And we'll do right. So, now we'll do right. If we do the right four, it's going to go from the right-hand side of the string and go left four. So, we're looking at the far four most right characters. Now, this can be useful in certain instances, but if I'm being honest, I don't use these that much. For the most part, I'm pretty addicted to using substrings. I love substrings. I think they're fantastic. And let's look at substrings like this. So, a substring is going to allow us to do a few different things. Let's do first_name. The second thing that we put within this function is the position that we want to start at. So, let's say we want to start at the third position. And then we specify how many characters we want to go. So, with this, we specified four, but let's just do two. So now, we're going to the third position and we're going over to the right two characters. Let's go ahead and run this. So, with Leslie, we get S L. So, we go one, two, and three. We start at the third position and then we take two characters, the S and the L. I have found this one to be extremely, extremely useful. Let's take this for example. Let's do comma. Let's do birth_date. And let's run this. I'm keeping everything in here, although it might be a bit much. But let's say we have this birth date and this middle column is the month. And we're running some query. We want to find the month that everyone is born. So, we can do that very easily using substring, and we wouldn't have been able to do this very easily using left or right. So, now we're going to take this birth date. We're going to use the substring and we want to select these middle characters. So, what we need to do, since it's all standardized, we do one, two, three, four, five, six. We start at position six and we want to select one and two. Let's go ahead and run this. And now we've pulled out all of the months. So, we can say as birth_month. And now we could save that, put it into a temp table, add it as a new column in our table, whatever we want to do. But now we have this information that we desperately, desperately wanted to know. So, that is left, right, and substring. Again, substring is fantastic. Now, let's keep going. The next thing that we're going to take a look at is replace. Now, replace will replace specific characters with a different character that you want. So, let's actually copy all this right here because I don't want to keep writing this out. And we'll say select everything. Now, what we're going to do is we're going to take the first_name and then we're going to say replace and then we'll also do the first_name. But we can specify what we want to replace and then what we want to replace it with. So, we have two more parameters that we need to put in this function. So, let's say a and let's replace it with a Z. Let's just see what that does. Let's go ahead and run this. And so now, when we see the letter A, and we are specifying a lowercase a, like Mark, that is replaced with a Z. So, that's really all replace does. It specifies what you want to replace and then what you're going to replace it with. Now, let's take a look at the next one. And we're going to take a look at a function called locate. So, if I say select and let's do locate. I'm going to give it a string. I'll say Alexander, that's my name. And I'm going to specify what I'm looking for. So, let's close this parenthesis. The string that we're actually looking for comes first. So, what we're going to do is I'm looking for the letter X in my name. So, we'll do X and Alexander. Let's go ahead and run this. And it tells us that it is in position four. So, we have one, two, three, and four. That's where our position is. That's where it locates that sequence that we're looking for. Now, if we pull this down here, replace this right here, and we'll change this locate. Now, let's say we're still looking at the first name, but we want to locate people that have an a n, like this, in their name. Let's go ahead and run this. And we get zeros for everybody except for Anne and Andy. So, this might be something where we put it into a CTE or a temp table, and then we can filter down based off of these results to where it's only equals one. Now, the last one that we're going to take a look at, and let's go right here. We're going to do first name, last_name. Now, this one is super, super useful because what we can do is have a concatenation of multiple columns. So, let's go down right here. So, we have first name and last name, but if we come down and we say concat, we can then combine these columns into one single column. So, we'll do concat, and we'll do first_name, and then comma last_name. And if we run this, it's going to be Leslie and Nope combined into Leslie Nope. Now, this doesn't look perfect, right? We don't want it to look like that. All we have to do is come in here and we can do a little space. So, we'll add a space in there. And if we run that, now we have Leslie and Nope, and we can call this as full_name. And this is something that I've done a million times in my real job where there's multiple columns, we want to create one column out of it, or take two columns and create one column. Happens all the time. So, this concat is really, really helpful to combine those columns really quickly. So, that is all we're going to take a look at in this string functions lesson. In my full course, you can find in the description below. I also have lessons on numeric functions, date and time functions, converting different data types, all in the functions module.
