Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome the module force. Equal programming to this is less than one sequel functions were currently in sub lesson, one point or string functions. And the previous lesson. We got to work with date functions a little bit and in the next lesson will actually define our own functions
00:15
for this lesson. We're going to, ah, deal with a problem that comes up from time to time. We're gonna pretend that our database has one name. Field, It doesn't have a first name field, it doesn't have a last name field. And now, for whatever reason, be it a report or a system transfer. We now need to move the first name and the last name to the new system,
00:35
which means we have the parts that name field.
00:38
For this lesson, we're gonna use the ACU Matic, a database. We're going to use the B account I or the B account table, and we're gonna pretend that a Q Monica doesn't have a field for the first name and last name. Even though it does on another table, we're gonna pretend that we have to parse out the name. So
00:57
that's that. Let's go ahead and get started
00:59
no.
01:00
So make sure you have a queue. Magica.
01:03
So make sure you have vacuum. Atticus. Set as your default database, Go to your sequel editor, new sequel editor
01:11
and let's start typing. So we're gonna select. I don't know what I want that someone dropped down to my from statement from which table do I want? I want to be accountable.
01:22
Okay, Amber say, because I know this. I happen to know this through my experience that the type that we want is e p
01:32
for employees because employees have a name.
01:34
Let's see what we get. Go first. Let's add a calm so account name. Let's run this to see what we get.
01:44
Yep, that looks like a bunch of names. Let's see what would happen if we, uh, shows everything except employees from this table.
01:49
Yeah, looks like we're getting a bunch of different types and names from businesses to states.
01:55
So let's get back to the employees we don't want apart. We're parsing names for people,
02:00
so let's get back to the people.
02:01
There we go.
02:04
Now let's take a look at a couple functions that we might use to accomplish this. First, we're gonna need the locate function so locates.
02:15
There's actually another way to do this. But we're gonna do it this way because this will show you the utility of the functions. Offer locates
02:23
account.
02:28
So the first argument is the comma. The second argument is the field of using
02:35
or text field
02:37
name.
02:38
I need a comma here, too. Don't forget that
02:42
as index Index is a special word. So put a dash or a not a dash, but am underscore at the end.
02:50
Let's go ahead and run this and see what we get back.
02:53
Okay? So if you look at this first result, we'll see 16. What that means is this comma is at the 16th position of strength.
03:00
Okay, so that's locate. Locate is a function. It does something very specific.
03:06
The specific thing that it does is it finds the index of the character, which is the first argument. And the string is the second argument.
03:15
The next function we want. Take a look at this sub string. You'll need to use that.
03:20
Now we need to pass in through the string that we're using, which is account, name and one. We're gonna start at one, and we're gonna go Three spaces,
03:29
A cz
03:30
Well, say, uh, last name
03:34
is the alias is going to run that
03:37
it's not really the last time we're gonna see what it does. So we have the string,
03:42
the starting position and how many characters to pull out so we can see that it started at the first position, which was an A, and pulled out three characters, which was a nd It's exactly what we expected to happen. So how is this going to work? Well, we're gonna use the locate function to define
04:00
the ending
04:02
the ending position of the substance because we're gonna pull out this first part of the name
04:09
and we're gonna use the locate function to provide the stopping point.
04:13
So let's do that. Let's copy this.
04:17
And it looks like they're all
04:20
the limited by a space. So let's change this comment to a space.
04:26
Let's go in and run this to see what we get.
04:30
All right,
04:33
I got a couple commas on the end there.
04:36
Um, that's because we're getting the last character. The last character for a lot of these is actually a space that we do not see.
04:43
Um, because it looks like all the white space. That's after it anyway. But it's there.
04:46
And then when it's not, it's a comma. But we don't want that comma.
04:51
So let's do a minus one here. So this will subtract one off off the character count to pull out. And it should pull off that last space for these guys in that last comma for that guy.
05:04
Let's go ahead and run that.
05:14
Try it again.
05:26
Okay, well,
05:27
so here's here's, ah, one of the problems that we're hitting. Um, this guy actually has a comma and a space as the ending
05:34
of his string, so we actually can't just do minus one. We'd actually have to do minus two to fix him, but that will ruin the other guys by chopping off there
05:45
their last character in the name. So
05:46
let's use the replace function.
05:48
What is replace do well, It's a function into something very specific.
05:54
It replaces a character with another character, so our first argument is the answer that we just got from running the sub string with the locate. Our second argument is going to be a comma,
06:05
and our third argument is going to be empty or nothing. So we're going to find the comma
06:12
in this thing that results
06:15
and replace it with nothing. And we're gonna call that our last name.
06:21
Okay?
06:23
And it looks like we have it. Okay, so we got the last name. We used a combination of three functions the locate, the sub string and the replace.
06:33
All right, so let's move on to the first name
06:38
and let this be a early lesson. If you're gonna create a database with name field, make it sure. You get that last name and the first name in there as their own fields to get filled in because it always comes up. Where? For some reason,
06:50
either a manager wants the first name and last name broken out. Or you're transferring to a system where you have to provide the first in the last name.
07:00
And if everyone is typed it in, they've all used different ways. They've done last name. First name, first name, last name, comma spaces, you name it. This is actually kind of consistent.
07:10
More consistent than if users were just typing it in.
07:14
So let's see. We want to get the first name now.
07:17
So what? We're going to d'oh
07:19
is we're going to
07:23
but the sub string
07:27
of the account name again.
07:30
And this time
07:34
we're going to use the sub string function with the accountant and field, and we're going to pace, then
07:43
the locate function
07:45
with the space in the account name.
07:46
Well, go ahead and run that
07:49
now. We actually have an extra space at the front of the front of the string, so let's get rid of that by adding one to it.
07:59
Okay, so you may have noticed that when this sub string we actually used to arguments when in the top version, we actually used
08:07
three arguments we had the field to start and the end and this one, we just have the field on the start.
08:15
And that's because the sub string function in my sequel makes the assumption that if you have not provided the third argument, it's because you just want to go to the end, Which is why we're getting this data back.
08:26
Um, now
08:28
again, we just want the first name. So we actually don't want the comma space, Mr that we're seeing.
08:35
So to make this simpler, we're actually gonna start to break it apart using some tools that we learned previously. The table expression as first piece.
08:48
All right. And then we're also gonna pull out thea
08:52
index off. So let's do it
08:56
a locate.
09:01
We want to locate the comma
09:05
in
09:07
this above string. So we're just gonna copy and paste
09:13
as position
09:18
and it's good and run that.
09:20
So this is at the eighth place of that name That looks right.
09:24
Joseph was already broken out because he didn't have a, uh,
09:28
Suffolk ce.
09:31
Um, so his count is zero. All right, so we're gonna use this.
09:35
Go.
09:37
So we're gonna say with table expression time
09:39
name chunks, Nicollet name junks
09:43
as
09:46
make sure we get this closed off.
09:50
Let's go ahead and select star from Maine chunks to make sure that everything's working so far.
10:01
Okay, It looks like it is. So we're gonna start to work with this to get the first name out.
10:05
So we already have the last name.
10:13
Okay.
10:15
I didn't run that.
10:18
All right. And now we need to work with the first name again. Now, the reason I turned this into a table expression of past it and layer down is because we're starting to get a long function chain, and those could be hard to read. So when they start to get too long for me, sometimes I start to break my concerns apart. I start to
10:37
spread out the complexity so that it doesn't turn into one giant long function, Shane,
10:43
and that makes it easier to read.
10:46
So what do we need to do? Well, there's some names where we don't want to work with it. We just want to pass the bac. So when position,
10:58
we use a case statement here now cases, Justin, if Dan statement for sequel. So its case.
11:05
What's the case when position
11:09
is less than one,
11:11
then
11:13
we just want the first piece.
11:24
Let's just end this.
11:26
Make sure this is working correctly,
11:28
Okay? So when and this seems to be working correctly, we're getting a first name
11:35
when there's no comma found. Okay, but that doesn't complete it because he only works for some people. So we need an l statement. So it moves and down to say else
11:46
and we're going to
11:50
we're going to
11:52
do the sub strain function.
11:56
We're gonna do the
12:00
first piece.
12:05
We're going to start at position one
12:09
and we're going to go to the position
12:16
on DDE.
12:18
Let's see what we get back for this. Sometimes you gotta move piece by piece. No pun intended.
12:24
Okay. Looks like we're almost there. We just have one problem. We have the comma at the end.
12:33
Well, let's go ahead and do minus one.
12:37
So this is going to be that numerical index position minus one.
12:43
And there you go, folks. I'm sorry this lesson started to drag out, but I wanted to show you some of the challenges you can face and how you can solve them using functions such a string functions and Channing to get them together. Thio
12:56
go over. What? We went over this lesson. We used a series of functions and table expressions to break out the first name and last name from a field
13:03
where it was the name combined into one. Sometimes it had a Suffolk. Sometimes it didn't, you know, sometimes had Mr or Mrs sometimes it didn't.
13:13
And what we did is we used the locate with the sub string,
13:20
and then we made a table expression when it started to get too complicated to bring it down another layer
13:26
and Then from that table expression,
13:28
we selected the data we were interested in.
13:31
We used a case statement, which is just an if dan statement for sequel, um, to decide whether or not we wanted to work with that
13:39
first name piece or not.
13:41
And at the end of the day, we achieved our mission is we were able to parse out
13:48
the last name and the first name. I hope you got that working and feel free to go ahead and practice because you know the old saying practice makes perfect. When you're new to this kind of stuff, this function chaining, it can look kind of weird and foreign.
14:03
And, you know, for a lot of people, that's just because it is. But once you see what it's working piece by piece, it starts to really fall in the place on how to use this stuff to your advantage to achieve what you want to achieve. Anyways, I hope you enjoy this lesson and I hope to see you in the next. Thank you

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor