So we're going to talk about the first of a handful of string functions in this video.
The string function we're starting with is called concatenate, which is short for concatenate or combining
strings or combining text together.
Now, recall that there's a bunch of these functions they all have to do with text.
If we go to the docs and you click on contact, you'll see an example of it.
But what you won't see is an example using any data and a tables.
Instead you'll see something like this select can cat and then just some text that's put in there.
Now this is kind of odd, but it's totally valid in the same way that I showed you way early on.
We can do something like this, select one plus four and use SQL as a calculator.
It's clunky, it's weird, but we get five as a result.
So most of what we're going to be doing with Can Cat is working with a table and concatenating different
pieces of text together from a table from different rows.
But we could start by doing something simple and concatenating two pieces of text or random text together
that has nothing to do with a real table full of data.
So it can cat will take.
If I just type it out here inside of parentheses any number of strings or pieces of text, like let's
just do some letters H and then E and then L just like that we're going to do Hello.
But I ran out of patience.
So instead we're stuck with hell.
And if I hit enter here, what we end up with is the concatenated result of those three text pieces,
those three strings shoved together to form one piece of text.
Now, why would we do that?
We probably wouldn't do it this way.
We wouldn't just go through all this work when I could have just done this right.
Select h e l.
That would have been a lot easier if I had my semicolon and we get the same end result.
But where it does become useful is if we're working with real data.
So we have our author first name or F name and author L name four last name.
Let's combine them together to get an author full name.
So we would concatenate the first name with the last name.
And as I said, what if we wanted full names?
We would use Concatenate and all these built in functions I'm showing you this term function means some
sort of built in repeatable operation and we will always have parentheses after the name of the function.
And then any values that we want to pass in will go inside the friends separated by commas as we've
already seen.
Right H, comma, E, comma, L instead of the parentheses.
And if you miss those parentheses, you're not going to have a good time.
You'll get an error.
As you can see here, it thinks can Cat is a column name, but really it's supposed to be a function
name.
But my school doesn't know that if we don't have the parents.
So we can concatenate one column with another column.
And that's exactly what I want to do.
Can cat author f name with author L name.
So let's try that select.
We still have to do select also, right?
If we just do can cat something like this.
It doesn't work.
That's not valid skill.
We have to have a select in there to get any results, so select can cut.
Author f name.
And let's just start simple and put in a couple of exclamation points at the end from books.
And here we go.
We see Jhumpa, Neal, Dave, Michael, Patty and so on.
So we've concatenated every author's first name with three x exclamation points.
But that's not what I want to do.
I want to instead concatenate the first name with the last name.
Author underscore l name.
Just make sure I have the correct column names and I'll hit enter.
And we're getting close.
We have Jhumpa Lahiri, Neil Gaiman, Dave Eggers, which I mean its first name and last name together.
That's what we said to do.
There's no space, though, and we probably would want a space in there if we were working with full
names.
Now, right now what we're doing is simply going through these string functions and seeing how they
work.
Is this a useful operation?
Maybe.
Maybe not.
Would we need a list of full names just like this?
Who knows?
But for now, let's say that's what our end goal is.
We want a list of full names and nothing else.
Well, what I can do is concatenate author F name, author L name with a space in the middle so I can
put as many things as I want in here.
I can hard code a string, like an empty string or a space between the first name in the last name.
Or I could put another column name in there too if I wanted, but I'm not going to do that.
I'll just do a space.
And now when I hit Enter, we have Jhumpa Lahiri, Dave Eggers, Patti Smith.
One thing also that's a little annoying is that the column name here is really nasty because it's not
an existing column.
It's not as if we just did select author L name.
From books because that is the actual column name and that's what we asked for.
But here we asked for this whole thing for each row.
And so the column name is not.
It's not nice to look at.
And there's a way better name for that.
Why not just full name or author name or something like that?
We know how to rename a column in our results using as we can give it an alias.
So I'll recall that line.
And then right after Mike and Cat, I'll say, as with one s as And then how about just author name
and there we are, we get author name and the same results.
It's just has a different title now instead of contact cat author F Name space author L name.
So that's a basic or a basic usage of content.
It will take whatever values we pass to it, whatever column names or whatever strings or pieces of
text, and combine them to form a single string for each row.
Of course.
So again, author f name with a space author L name would give us Dave Eggers and Jhumpa Lahiri, and
then we can simply select that from a table, which is what we've already done.
But there's another flavor of kin cat, which is kin cat W s and there is an underscore there and it
doesn't stand for whitespace.
It is width separator.
So this works a little differently.
It's the same concept.
It will can cat whatever we tell it to, but we first provide as that first value what we call the first
argument.
We provide a separator and whatever that separator character is will be smushed between every other
value.
So let me show that without even working with a table, let's just do a select can cat underscore W's
and let's do something like an exclamation point.
And then and here I'll do hi fi lol.
I don't know, a couple of strings, so if I select that, what do we end up with?
Hi by lol.
Well lol doesn't have an exclamation point.
It is only going to be inserted that exclamation point between each piece of information, not afterwards
and not before the high.
So if we compare that with just a regular contact.
We end up with all of those pieces of tech smushed together.
Nothing changed.
Just cram them together as is.
But when we add with separator was that first value is the separator character and that is smushed between
each of the subsequent values.
And that's how we get high exclamation by exclamation lol.
So we could use this to do something like create a unique slug.
Basically part of a URL that contains the title, the author F name and the author ll name all separated
by a dash.
So why don't we try that?
We'll do a select and I'll do this on a separate line.
Sure can cat w s.
I want to separate them using a dash or is that a hyphen?
I never remember, but whatever that is, that line character will take the title and then the author
F name and the author ll name.
From books.
And there we go.
We get The Namesake, Jhumpa Lahiri, and there are spaces in our book names, so it doesn't exactly
work.
Right, because this wouldn't be a valid URL.
We will learn how we could possibly replace spaces later on.
But for now, this is fine.
It's a silly example just to show that if we use can we use whatever separator we provide will be inserted
between each of our concatenated pieces of text?
All right.
That can cat and can cat.