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 are actually dive into the meat 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 as well string
functions as string functions excuse that and we hit enter here.
You can see I've already clicked on this link.
The Maia's well and reference manual string functions.
I have to say I'm not a huge fan of the documentation of my ask.
Well 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 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 will be able to be combined
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 mice and people who work with my ask
you out 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 Concat.
So what it will do is combine pieces of data.
It's combining strengths.
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 a 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 Juba.
Here we are 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 do we combine that.
How do we get that.
The answer is we use Concat.
So let me just first show you what we have right now.
Well do a select authored 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 here's a slide.
I totally got to have them myself.
What if I want full names.
So concat the way that concat 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 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 concat a column name with another column.
So that's what we'll work on.
We're going to do first name or author name and author L name and combine them together.
And if we do this right here.
So concat author name and author L name what you 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 jubilate here.
So there's no you know that's not exactly what we want maybe quite 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 it 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 name and that will give us Dave Eggers Juba the
here.
So now that I've shown you the slides.
Let's take a look.
And the first thing I'll show is that often people expect they can just come in here and just type Concat.
But if I just do concat 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 Arthur F. name coming from an author 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 select concat 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 concat Hello world and I hit enter.
You can see it does it for me and it just gives me.
Hello world.
Combined these two things.
So I can do the same thing in outer space and rather than a space with two dots it is easier to see
and we need a comma.
A lot of punctuation going on.
Select concat Hello comma dot dot dot comma world.
It just shoved them all together and we get.
Hello 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 you can cut a column like author 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 concat
author name comma space comma author Al name.
But then in here we have the data that we're looking for.
We have for the theory we have Patti Smith Raymond Carver and so on.
So that's really useful to combine data like that.
Now if you want to get a little fancier Let's rename it so let's give it an as.
And we'll just say as a full name just like that.
Now I get full name up here.
Jim Pilla here in the game and so on.
So that's Concat.
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 will do author first name just on its own author name as first name.
Let's just do first actually as first comma author L name has lapsed and then we'll have a concat author
and let's do it on new line just because it gets a little messy here.
Concat author f names.
This is what we've already seen with this space comma.
Author L name.
And we'll call it full so we'll have three different things printed out the first name under the title
first author L name at last.
And then the full name combined using concat 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 and 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 see this as we go.
We're not going to just use concat and you don't have to just use one concat per selects 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 found 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 a comma here.
All right so one more thing about concat is that there's something called concat ws which stands for
concat 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 is a shortcut for that.
So if I wanted to have title dash author named dash author last name let's not debate why but if I wanted
to I could do something like this.
Select Concat.
And we have author.
Actually we started with title comma and then we wanted to 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 her repetitive typing especially if we have more items that we're combining.
But if I just do this year from books you see it does what you expect puts the title dash first.
Last but we can use concat WS 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 a 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 named dash authorize.net.
So now if I pace that in you can see if we get the same result.
Although I added spaces here but it's the same idea.
It's sandwiched every time.
So just worth noting if you ever are in a situation where you're combining lots of data in the same
manner you can use concat ws to save you typing this or whatever your symbol is.
Over and over.
So that's our first string function.
We saw concat and Concat W-S and what they do is concatenate or shove together smash together strings.