All right.
So we have that out of the way.
We've seen how to run files using the source command.
And then we actually did that with the sample data that I gave you.
Now we actually dive into the meat of the section, which is all about string functions.
And the first thing I want to show you is that if you go to Google and we type in my SQL string functions,
oops, string functions, excuse that and we hit enter here.
You can see I've already clicked on this link the my SQL reference manual string functions.
I have to say I'm not a huge fan of the documentation of my SQL.
For one thing, I think it's kind of ugly and looks dated, but it does have all of the information
you need.
I just don't really like how it's presented.
But if you if you look at this giant list here, these are all string functions that we can use.
And a lot of them are very, very specific.
I'm going to show you some of the more.
Well, first of all, the useful ones and then a couple just fun ones and that we'll be able to combine
together for some good exercises.
But we're not going to take a look at most of these.
Most people don't even know that these are here.
I'd say that's a generalization.
But in my experience, working with my SQL and people who work with my SQL, you use maybe five of these
on a regular basis, and then occasionally you might need to do something like, Hey, is there a way
of doing X?
Let me go check on the documentation and oh, look, there is a way of doing that perfect.
Or Oh no, there's not a way.
Great, what am I going to do next?
So I just want to show you that this is here.
And the first one that we're actually going to take a look at is called Cat.
So what it will do is combine pieces of data to combine strings.
So here's an example using this book data, remember that we have author first name and author last
name.
And there are valid reasons for separating that data.
Sometimes, I mean, you might want to sort books by last name and sometimes you may only want the last
name in certain contexts.
You might not want the first name.
It's not a great example, but there are reasons that you would separate this out.
Just like when you're filling out a form online, you're signing up for a site.
They usually don't just ask you for your full name.
They ask you for your first and last name.
But then if they have those pieces of information, it's pretty easy to combine them.
So that's what we're going to see now.
How do we combine these two and get something that says Jhumpa Lahiri or Neil Gaiman or in this case,
Foster Wallace as the last name, which I'm actually not sure if that qualifies as a last name.
Exactly.
But David Foster Wallace, how did we combine that?
How do we get that?
And the answer is we use Concatenate.
So let me just first show you what we have right now.
We'll do a select author f name, author l name from books.
And that's what we get here.
Just what I had printed out in the slides.
So what we want again is to combine them.
But we'll take some baby steps and yep, here's a slide I totally got ahead of myself.
What if I want full names?
So can Cat.
The way that Kin Cat works is that we pass in multiple arguments or pieces of data and it will combine
them.
So whatever we give it in this case, I mean, if you had columns named X, Y and Z, this would work.
But it's really just supposed to be an example.
More specifically, what you can do are things like this can cat a column name with another column.
So that's what we'll work on.
We're going to do first name or author f name and author l name and combine them together.
And if we do this right here, so can cat author f name, author l name.
What it will actually do is just slam them together, put them exactly together, concatenate them so
there's no space in between.
So we get Dave Eggers.
Jhumpa Lahiri.
So there's no that's not exactly what we want.
Maybe we want a comma, maybe we want last name, comma, first name, or maybe we want first name,
space, last name.
So there's a way of doing that.
When we can cat, we can pass in a column name and then some other text and then another column name.
And if we wanted more text, we can keep going on and on and on and it will concatenate everything.
So to add a space, it's pretty simple author, first name, comma, and then a space comma author l
name.
And that will give us Dave Eggers and Jhumpa Lahiri.
So now that I've shown you in the slides, let's take a look.
And the first thing I'll show is that often people expect that they can just come in here and and just
type canned cat.
But if I just do canned cat, well, author f name, comma, author l name.
There's a problem.
Hopefully you can identify what the problem is.
If I hit enter, this is not valid syntax.
First of all, it doesn't know what table.
What if we have multiple tables?
Where's author if name coming from an author l name.
And second of all, we have to select.
So we can't just run these willy nilly.
We can't just run them on their own.
We need to run them as a select.
So select can cat.
And then in here, I'm actually going to start with something simple.
I'm not going to use columns, I'm going to use text.
So I'll just put something like Hello Comma World.
So this is very trivial, but if I just do this select can cat hello world and I hit enter you can see
it doesn't for me and it just gives me hello world combines these two things so I could do the same
thing and add a space and rather than a space, let's do dots so it's easier to see and we need a comma
a lot of punctuation going on.
Select can cat hello comma dot dot comma world.
It just shoves them all together and we get hello dot dot dot world.
So that's how we can do it with just text, which you probably won't be doing.
You'll be doing things like this select and then can cat a column like author f name and then a space
and then author l name from books.
So let's try it.
We can copy it.
Now.
Go over here and paste it.
Select.
And what it will do when we hit enter is just make a new column for us or in our results.
It doesn't change our initial data.
Just to be clear, our data looks the same in the database.
This is purely about printing it out.
But we have this new thing up here with this horrible title can cat author f name comma space comma
author l name.
But then in here we have the data that we're looking for.
We have Jhumpa Lahiri, we have Patti Smith, Raymond Carver and so on.
So that's really useful to combine data like that.
Now, if we want to get a little fancier, let's rename it.
So let's give it an as and we'll just say as full name just like that.
And now I get full name up here.
Jhumpa Lahiri, Neil Gaiman and so on.
So that can Cat.
I'll show you a couple more examples just to show you how these string functions work.
You don't have to just do one at a time.
We could do something like this select and we'll do author first name just on its own.
Author F name as first name.
Let's just do first actually as first comma, author l name as last and then we'll have a cat author
and let's do it on a new line just because it gets a little messy here can cat author f name.
So this is what we've already seen with a space comma author l name.
As and we'll call it full.
So we'll have three different things printed out.
The first name under the title, first author LL name as last, and then the full name combined using
cat as full.
So then we need to say from where?
From books.
Now I hit enter and I get this pretty little table first last in full.
So what I want to show is that you don't have to use it in isolation.
You can combine it with other things and you can and we'll see this as we go.
We're not going to just use Cat and you don't have to just use one can cat per select.
You could have multiple if we wanted to, you know, I could have full name and then I could have full
names comma separated instead.
And I want to do that.
You can probably figure this out, but rather than just having a space there, I'll just put a comma.
And now I get Jhumpa Lahiri.
All right.
So one more thing about Venkat is that there is something called Venkat WC, which stands for Canned
Cat with Separator.
So there might be times where you're concatenating, concatenating, multiple fields, multiple things
together with the same symbol or piece of data stuck in between them.
So there's a shortcut for that.
So if I wanted to have Title Dash, author F named Dash, author last name, let's not debate why.
But if I wanted to, I could do something like this.
Select Venkat and we have author or actually we started with title comma and then we wanted a dash and
then author F name comma and then another dash comma, author l name just like that.
So it's kind of a lot of repetitive typing, especially if we have more items that we're combining.
But if I just do this here from books.
You see, it does what you expect.
It puts the title dash first, dash last.
But we can use contact us and it looks like this.
So it switches things around a bit.
The first thing that we pass in is what we want the separator to be.
So in this case, a dash with the space on either side and it will go ahead and then insert that in
between every other thing we pass in.
So we'll have title.
Dash Author First Name.
Dash Author last name.
So now if I paste that in, you can see we get the same result.
Although I added spaces here but at the same idea it send which every time.
So just worth noting, if you ever run a situation where you're combining lots of data in the same manner,
you can use us to save you typing this or whatever your symbol is over and over.
So that's our first string function we saw can cut and contact us.
And what they do is concatenate or shove together, smash together strings.