While we're on the topic of functions, I want to show you something else. You're going to have to put these things together yourself and get creative. You can't get creative until you've been shown some examples and you've seen some examples, and you've seen explanations of examples, and why they work, and how they work, which is what I'm doing. Let me show you one fun one. If I do select empname, empname, empid from employees. You'll do that or run this. Notice, John Adam, James Monroe, Serena Williams, John Williams. You see the first name space last name. You notice that? It's empname. I don't have a first name, last name split up. What if I wanted to do that? What if I wanted to split up the first name and last name? How would I do that? I can use this space right here, the format of this column, and the data in this column tells me that it's a first name space last name and, they're all the same way. All I have to really do is get a function, use a function that will find the space. I know that before the space everything is the first name and after the space everything is the last name. You notice that? That's a pattern I'm going to be going by. Let me show you what I mean by that some more. How do I get a function that will find me where the spaces are? Well, let's see this called in string. If I say select empname, instring empname, and where's the space in empname from employees like that? When I run this, I notice something, it says five, notice 1, 2, 3, 4, 5. In this instance, a space is at the fifth character. Here's at the six character, isn't it? 1, 2, 3, 4, 5, 6 character is a space. Over here, it's the seventh character, six and then seventh of the space. Over here, it's the fifth character, four and fifth character. Notice that? This right here, instring tells me the numeric value where the space is. Let me show you something else. If I wanted to find out, how do I determine everything before the space? How do I tell it everything before the space is the first name and everything after the space is the last name? Well, let's see. I can use something called substring. If I say select empname and I say substring, and I'll just do the first one. I'm just going to say empname, start at the first character, end at the fifth character, or maybe the fourth character in this case. So it's going to give me John, J-A-M-E, S-E-R-E, J-O-H-N because I said start at the first character and give me four characters. Let's say from the table called employees, take a look at this when I run this, maybe I should do this the right way. It's substring not I-N-G, S-U-B-S-T-R, there we go. So John and John, because there are four characters, J-A-M-E, the first four, S-E-R-E, the first four. Well, I can't put in a four here. I really need to put in where the space is. I want to say start at the first character but I want to stop where the space is. So all I can do, I can grab that, and I can just replace this right over here. Just like that. When I run this now, I get John, James, Serena, and John. You see that? Well, let me expand this a little bit so you can understand what I'm talking about. This right here tells me where to stop, how many characters to get. This right over here tells me where the space is, doesn't it? That instring, this tells me where the space is. It's essentially saying look in the employee column and tell me at which character you find the space. It tells me it finds a space of the fifth character for John and sixth character for James, seven character for Serena, and fifth character for John again. Then I'm just saying start at the first character and stop wherever you find a space and I'm getting the substring or a portion of the string. I want you to get the substring of empname start of the first character and I want you to stop where the space is and I'm going to say this is going to be first name, that's just aliased as first name. Watch what this did. You notice the name of the column here instring, empname, it looks weird. When I run it now, I should say this right way, quote, quote and I run this now. Notice I get first name. See that? It gave me an alias for the column right here, the proper alias for the column. So that's the first name. How do I get the last name? Well, I don't want to start at the first character for the last name. I want to start where the space is and get the rest. So if I just do substring empname, I'm going to start where the space is, instring empname, where the space is, just like that. I'm going to say last name just like that. I'm going to start at where the spaces and I'm going to add one because I don't want the space in the name. When I do this and I run this, here's the last name and there's first name. Notice that? That's using two functions, substring, instring to retrieve or split, if you will, the first name and the last name from the full name empname. Isn't that great? That's using a couple of functions right within the select statement to get the desired result. Hopefully you had fun and you learned something.
