4.3 String Functions

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.