In this video, we're going to get extra fancy and combine both can cat and substring together and use
them in conjunction to do something like this right here.
A shortened version of the title, which is just a substring concatenated with dot, dot, dot at the
end.
So we know how to do both of those pieces individually.
Let's say I want to get this substring of a title.
Well, how many characters is this?
One, two, three, four, five, six, seven, eight, nine, ten, I believe.
So we'll get the first ten characters.
We can start there and I'll do it over here for now.
Just in my interactive shell select sub.
Let's do the short version str.
Remember, either one works.
And this is the title.
I want to go from character one for ten characters from books, and let's see if that works.
We get the name, say, Norse myth, American G.
Okay.
But what I actually want to do is also add the dot, dot, dot there to make it clear that this is a
shortened version.
It continues on and we're looking at the first ten characters.
So what I want to do is concatenate this with three dots.
So how do I do that?
Well, it's easier if we work in a separate file.
So you could work in any old SQL file you want, and then you could just source that file.
That would be one option.
Or we can work within one of our editors, which I think is what is easiest for most of my students.
So instead of selecting just the substring like that, I'm going to select the cat.
Of this substring.
Comma.
Dot, dot, dot.
From books.
What's happening here is that this will run first and this will be replaced with something like American
GI or whatever one of those sub strings was.
And then that will be concatenated with the dot, dot, dot.
So I can run it from within here if I want.
Let's just run this and then we go.
We see our results.
The name said dot, dot, dot Norse myth.
Dot, dot, dot.
I'll just run it over here.
Same thing.
Although if we look at my initial screenshot, I renamed it to be short title, so I probably would
do that as well as short title from books.
Let's run it again.
And there we are.
That's much more palatable.
So there's a couple of notes here.
One is make sure you're comfortable with this concept of or that you at least can explain what's happening
of passing through a function result to another function.
It's a little weird if you're not familiar with this, but we've got nested stuff and the innermost
thing will be evaluated first for each row and then that will be passed to concatenate.
But you can also think of it and you can reformat it to look like this.
If it helps you, you can indent this like that right there and then do something like that.
So it's not exactly perfectly formatted, but here we're basically saying can cut these two things together
here.
Let's do it like this.
Can cat these two things this with this and call it short title and if it helps you understand how it
works by having it nested and having it indented, by all means do that for longer queries.
That is definitely worth doing.
This one's not too long, but still, hopefully you are able to understand how it works.
Now let's try one more example.
Why don't we create a column that is an author's initials like this?
Jhumpa Lahiri would be J period.
L Period.
So how am I going to do that?
Well, how do I get J.
Let's start there.
We need to select the substring.
Of author.
F name for first name starting at character.
One for one character.
Right.
And that should give us the first initial and then let's do the same thing for last name.
So that would be author L name starting at character one, moving forward for one character from books.
And I guess we can just run this one line.
If I'm in the workbench, remember I click the second lightning bolt and it only runs where my cursor
is.
So we see J.
L That's Jhumpa Lahiri.
Neil Gaiman is and G.
You could verify this by Just Remember the order.
So that's getting us those two initials.
But I want them to be combined so I can use concatenate, I can concatenate these two things.
So I think it will be easier if I do this on a separate row I'm going to contact.
This whole thing, comma, this whole thing.
And now if I run this line.
We get them smushed together.
J.L. and G.
And G.
Jld for Dave Eggers.
Now we're almost there.
I want to add a period before and after, and remember, with Kit-Kat I can just put any old string
I want in there.
So there's a period between the F name, initial period, last name, initial, and then one more period
at the end.
Just make sure I put it in quotes.
So let's try running this.
And there we are.
We see J or J dot, l dot.
And then I could even rename this whole thing as.
How about initial or author initials?
Run it again.
And my output here we see it's really hard to see the name author, the initials up there.
Thank you for that SQL workbench.
Let's try it over here.
There we go.
Author initials.
So we're combining concatenation with sub strings.
Kind of cool stuff.
Also possibly confusing.
Just take your time trying to walk through it and let me format these to look a little bit nicer.
Here's a reformatted version so it's just slightly easier to read.
You can see that this is going to be done first.
That will be concatenated with the dot, dot, dot.
We're renaming that whole column that we're creating from books and here, same deal, right?
This will happen and this will happen.
Those will be replaced with some letter and some other letter.
And then they're concatenated with a dot and another dot and we call the result author initials.
So again, confusing, but hopefully you can take your time and step through it.
Next up, we're going to move on to some other built in string functions.