 What's going on everybody? Welcome back to another SQL tutorial. Today we're gonna be looking at string functions. Some of the things that we're gonna be looking at are things like trim, replace, substring, and upper and lower. We're gonna create a new table, insert a little bit of bad data into it, and then we're gonna be using that to work on our string functions today. So, I already have this set up right here. I'm gonna put this in the Githubs that you can just download this. You don't have to type this out manually. So go look in the description if you know, you just wanna get that off the Github and download that and copy and paste it, save you a little bit of time. But let's go ahead and run this really quick. And as you can see in this table, we have our data right here. Give me one second. So in this employee errors table, basically what we have, actually let me pull this back up. Basically what we have is in this first one, we have, here we go, we have some basically blank spaces on the right side. In the second one, some blank spaces on the left side. We also have Jimbo, which is an error because the name is Jim and Halbert because the name is actually Halbert. And then for Toby, for whatever reason, that O is capitalized. And then Michael got in here and added this extra part. So we're gonna have to figure out a way to take that out when we're doing our query. And that'll come in a little bit later, I think in the substring section. So let's get into it right away. Let's start using our left trim and right trim. We're gonna kind of go through each one pretty quickly. Hopefully, I'm not trying to make this a super long video because we got a lot of things to get through in this one video. So I'm gonna go through the trim, right trim and left trim. Let's look at the employee ID because that's the one where we have some blank spaces on the right and the left side. The left side you'll be able to, obviously you're gonna see that one much easier, but let's start walking through this. So let's do select employee ID. And before we get any further, let me just get the employee errors on here so we can see everything as it comes up. So we're just gonna do trim and then type in the column that we want to take these blank spaces out of. That's what the trim does. The trim gets rid of blank spaces on either the front or the back or the left and the right side. So on both sides, that's what trim does. And we'll say as ID trim. So let's run this one really quick. And as you can see, this is our regular employee ID. And so you can't visually see it as easily on this first one, but there are blank spaces after this 1001 and we got rid of those. And then there were blank spaces before the 1002 and we got rid of those. Now I'm just gonna copy this two times because it's basically the exact same thing, but I'm gonna show you them all at the same time. So it's the exact same thing except L trim and right trim. And let's take a look at all these at the same time. And let me pull it up. So in the, let me see if I can get these all in here. Okay, in the trim, it got rid of both the left and the right side. So all of these were fixed. In the employee ID for the left trim, we're only gonna be getting rid of this one. This one still has blank spaces on it. And when we do the right trim, we're only gonna get rid of the stuff on the right side. So this one doesn't change because this is on the left-hand side where the blank spaces are. So this one was fixed. Again, it's not super visual, so you can't really see it, but that one is fixed. Let's move on to the next part, which is using replace. So for this one, we're gonna be looking at the last name. So let's go back up really quick to the employee errors. As you can tell, the last name, the biggest one where we kind of wanna take something out of because we don't want that dash fired still in there. We're gonna replace that. And so let's look at how to do that. Let me just copy this real quick and get rid of this top part. So we're gonna do the last name. So let's just start off with our last name. And then just as a baseline, so you can see what it looks like before. And then we'll do replace. And all we're gonna specify is the column that we want to do the replacing in. We're gonna specify the value that we want to replace. So in this, it's gonna be dash fire. Oops, got a little aggressive on that one, dash fired. And we're gonna indicate what we want to replace it with. Now I'm just gonna replace it with blank. And we can say as last name fixed. So let's see what this looks like really quick. And it looks like it worked. So in this last name, it originally had Flenderson at dash fired. And when we replaced it, and we took that dash fired and replaced it with basically nothing, it then fixed it. And so now it looks correct. All right, let's move on to the next one. I think this one might be the longest one to write, but that is the substring. And let me take this real quick, trying to save us some time. So substring is very unique. You can specify in either a number or a string. You can specify the place that you wanna start. And then you can also specify how many characters you wanna go out. And it pulls that in. So just as a really quick example. And then I'm gonna show you kind of a use case for this one that I think is pretty cool that maybe, let me see. So that maybe that you'd find useful. So I'm gonna do first name, and then I'm just gonna do one comma three. So it's gonna take the first name. It's gonna start at the very first, very first letter or number. And it's gonna go forward three spaces or three spots. So let's just take a look at what that looks like. So for our table, it's gonna take Jim, Pam, and Tob, or Tob, or Toby. And so it's only gonna take the first three, because you're starting at number one. Now what if we started at three? So we do three comma three. It's gonna go to the third digit or third letter. And then it's gonna go forward three. So you kind of get a sense of how this works. Now I'm gonna show you something that I think is very interesting that I think you guys will also find interesting. Let me fix that, because I just messed it up. So if you've ever heard of something called fuzzy matching, now if you don't know what fuzzy matching is, I'll give you an example. Let's say in one table, my name is Alex. And in another table, my name is Alexander. If we tried to join those two together based off of my name, they will not join because one is Alex and one is Alexander. They're not an exact match. But if I take the substring and start position one and move forward four characters, it's gonna take Alex from both and then it will match them together and say that they are the same. So that, you know, it may not be perfect. That's why it's called a fuzzy match because it can work for a large majority of the time, but it's not gonna work every single time. And so I wanna show you how we can use this here. Really quick, I need to join this to the demographics table. So I'm gonna do that really quick. Bear with me for just one second. Let's try to make this at least look somewhat good. So what I'm gonna do is, I'm gonna start off by saying, let's tie it to the first name. Let's do, whoops, let's do error.firstName is equal to the demographics table first name. Okay, so I wanna see, and I'm just gonna do firstName for error and let's do firstName. So let's see what comes up when we do it like this. So the only one that is gonna work is Toby and that's because even though it has a capital O, it's still gonna take it. So, you know, we wanna get all of them to match and we can do that, but it's gonna be in a little bit of a different way than maybe is perfect, but that's why they call it fuzzy matching. So we're gonna use substring on this. So I'm gonna say substring. Oops, let me spell that right. So I'm gonna say substring and we're gonna go one, three. So starting at the first position and going forward three and we're gonna do the exact same thing on the, oops, substring would be great if I could spell that correctly. We're gonna do the exact same thing. So one and three. So we are actually going to take this. Give me a second, miss that. We're gonna take this up here and we're just gonna go like that. And I don't, why did I copy it with the error? Okay, so let's run this really quickly. And as you can see, it is now gonna match all of them. And you can do this on a lot of different things. Typically when I'm doing a fuzzy match like this, I'm not just gonna do it on a first name, right? Because there can be a ton of people named Jim. You know, we wanna do it on, and real quick, let me actually show you what the originals looked like just to make sure I hit the point across. And that is gonna be first name and come, all right. So real quick, I have to actually look at this. So it originally was Jimbo, Pamela and Toby. In this one was Jim, Pam and Toby. And so when we just took the first three because it was Jimbo, it then becomes Jim, it was Pamela, it becomes Pam, now it matches. And so that's kind of the example that we're going for. Like I was saying, I typically will not just filter on a first name because there's gonna be a ton of people named Alex or Jim or Henry or whatever. You're gonna do this on many different things. So I would be doing it on things like, if I'm trying to do a fuzzy match on a person, I do it on their gender to make sure that their gender is the same. And I wouldn't probably need to use a substring for that, but just to kind of give you a little bit more information, I need to do it on the last name. So I'd need to use that substring again. And I would probably do it on the age, oops. What am I doing? Come on. The age and the date of birth, okay? So all of those things, if you fuzzy match on the first name and the last name and then the gender, the age and the date of birth are all the same, then you can typically get a very high accuracy in matching people across tables. Whether or not you have, this is an example if you don't have an employee ID, which is what we do have, but take for example, we were not given that. This is a way to match them using substrings. Let's move on to upper and lower. All upper and lower is going to do is basically take all the characters in the text and make them either upper or make them lower. So it's very self-explanatory. Let me copy this up here. And we will get going on this one. Let's just look at the first name. Specifically, we're gonna be looking at Toby right here. So let's do first name, let's do lower. And all we have to do is put in the column that we want to do. So this is our original first name and it then takes every single string that is in here or every single, I guess, character and it makes it lowercase. That's all it does. And it is the exact opposite when we do upper. So we can now take a look at this one and now everything's gonna be capitalized. So there is a lot that you can do with these string functions and this is not all the string functions that there are, there are a lot more, but I would say that these are the more popular and more useful ones that I typically use on a regular basis. And so I hope that this has been helpful. I hope that you've learned something from this. If you did, be sure to like and subscribe below. I have a lot more videos coming out with tutorials on everything from SQL, Python, Tableau and Excel. Thank you so much for joining me. I appreciate it and I will see you in the next video.