Advanced SQL Tutorial | String Functions + Use Cases

what's going on everybody welcome back to another sql tutorial today we're going to be looking at string functions some of the things that we're going to be looking at are things like trim replace substring and upper and lower we're going to create a new table insert a little bit of bad data into it and then we're going to be using that to work on our string functions today so i already have this set up right here i'm going to put this in the github so that you can just download this you don't have to you know type this out manually so go look in the description if you know you just want to 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 uh basically blank spaces on the right side and the second one some blank spaces on the left side uh we also have jimbo which is an error because his name is jim and halbert because his name is actually halpert 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 uh our left trim and right trim and we're going to 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 going to 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 going to 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 um so that 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 know 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 going to copy this two times because it's basically the exact same thing but uh i'm going to show you them all the same time so it's the exact same thing except ltrim 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 going to be getting rid of this one this one still has blank spaces on it and when we do the right trim we're only going to 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 going to 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 um the biggest one where we kind of want to take something out of because we don't want that that dash fired still in there we're going to 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 going to do the last name so let's just start off with our last name and then just as a baseline so we can see what it looks like before and then we'll do replace and all we're going to specify is the column that we want uh to do the replacing in we're going to specify the value that we want to replace so in this it's going to be dash fire oops got a little aggressive on that one dash fired and we're going to indicate what we want to replace it with now i'm just going to 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 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 is very very unique you can specify um in a either a number or a string you can specify the place that you want to start and then you can also specify how many characters you want to go out um and and it pulls that in so just as a really quick example and then i'm going gonna show you kind of a use case for this one that i think is pretty cool that um you know maybe let me see [Music] 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 going to take the first name it's gonna start at the very first um 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 going to take jim pam and tub or tobe for toby um and so it's only going to take the 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 going to go to the third um digit or 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 going to 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 try to join those two together based off of my name they will not join because one is alex and one is alexander there's no they're not an exact match but if i take the substring and start position one and move forward four characters it's going to 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 going to work every single time and so i want to show you how we can use this here [Music] really quick i need to join this to the demographics table so i'm going to do that really quick [Music] bear with me for just one second let's try to make this at least look somewhat good so what i'm going to do is i'm going to start off by saying let's type to the first name let's do whoops let's do err dot first name is equal to the demographics table first name okay so i want to see and i'm just going to do first name for error and let's do them the first name so let's see what comes up when we do it like this so the only one that is going to work is toby and that's because even though it has a capital o it's still going to take it um so you know we want to get all of them to match and we can do that but it's going to be um in a little bit of a different way than maybe is perfect but that's why they call it fuzzy matching so we're going to use substring on this so i'm going to say substring oops let me spell that right so i'm going to say substring and we're going to go 1 3 so starting at the first position and going forward 3 and we're going to do the exact same thing on the oops sub string would be great if i could spell that correctly i'm gonna do the exact same thing so one and three so we are actually going to take this give me a second missed that we're going to take this up here and we're just going to 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 going to 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 going to do it on a first name right because if every there can be a ton of people named jim you know we want to do it on uh and real quick let me actually show you um what the originals looked like just to make sure i hit the the point across um and that is going to 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 and 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 what 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 going to be a ton of people named alex or jim or or or you know henry or whatever you're going to do this on many different things so i would be doing it on things like uh 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 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 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 you know this is an example if you don't have like 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 sub strings let's move on to upper and lower all upper and lower is going to do is basically take all the characters in the the text and make them either upper or make them lower so it's very self-explanatory let me copy this up here [Music] and we will get going on this one [Music] let's just look at the first name specifically we're going to 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 and it makes it lower case 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 going to 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 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 in the next [Music] video

Channel: Alex The Analyst

Views: 11,608

Rating: 5 out of 5

Keywords: Data Analyst, Data Analyst job, Data Analyst Career, Data Analytics, Alex The Analyst, string functions, sql string functions, string functions in SQL

Id: GQj6_6V_jVA

Length: 13min 48sec (828 seconds)

Published: Thu Feb 04 2021