 Hello everybody. 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, 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 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, we say select everything from employee underscore demographics. And let's add a semicolon here. And let's run this one right down. Now, what we can do is we can look at how long each person's name is. What we can do is just take the first name. But then we'll also do the length of the first underscore 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 anywhere above 10, I will go and specifically look at those and try to clean those and fix those up. Now let's go on to the next one. And these next ones are pretty simple ones. At least I think they're fairly simple. We're gonna 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 gonna do sky. If we select upper sky, it's gonna give us an all upper case. Or we can copy this. And we can do lower. So now let me add semicolons otherwise it's gonna drive me crazy. Let's try this lower now. It's gonna do all lower even if I make it all capital. But if I say all capital sky, it's gonna 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. 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 om. 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 gonna 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 gonna do is we're gonna 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. Let's run this and add our semicolon. That's gonna be the end of me in this lesson. I was 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. But you can see that there's spaces before and there's spaces after. But that's what trim does. Trims 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 gonna 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 our 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 gonna 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 gonna 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. It's at our semicolon. Now I'm gonna run this. Now I want to get the first name. And I'm gonna do left of the first underscore name, just like this. Now when you're using this is actually gonna be an error. Let's see if I highlight over this if it'll tell me what the error is. It says the parentheses 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 values actually a number. We're gonna 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. Go ahead 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. I 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 gonna 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 underscore 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, 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 SL. 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 underscore date. And let's run this. I'm keeping everything in here, although it might be a bit much. Let's say we have this birthday, and this middle column is the month. And we're running some, you know, 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, and 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 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 underscore month. And now we could save that put into a tent 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 it's 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, 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 underscore name, and then we're going to say replace. And then we'll also do the first underscore 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 lower case a, like mark, as replaced with a z, that's really all replaced does 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 parentheses. 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 in Alexander. Go ahead and run this. It tells us that it is in position four. So we have 123 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, place 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 and and Andy. So this might be something where we put it into a CTE or a temp table, then we can filter down based off of these results to where it only equals one. Now the last one that we're going to take a look at, and let's go right here, we're gonna do first name, last underscore 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 underscore name and then comma last underscore 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 could do a little space. So a lot of space in there. And if we run that, now we have Leslie and Nope, and we can call this as full underscore 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.