Next up in this onslaught of string functions is substring.
So substring allows us to select individual parts or portions of a string.
I only want the first character.
I only want the middle ten characters or the last character or so on.
So the syntax is similar to King Cat.
Here's an example.
In this case, we're not working with columns or tables or anything.
We're just working with static data.
Hello world.
And with this will do select.
So there's our select again and then substring our string function in parentheses.
In this example, the first thing we do is tell it.
Here is the string we want to work with.
So that first thing is Hello World.
And then what we're going to do is only print out the substring the portion from 1 to 4.
So those are indices referring to characters and in my SQL they start at one.
If you're familiar with other programming languages, they often start at zero.
That's not the case here.
So H is one.
So we go from 1 to 4.
So one, two, three, four, which gives us hell.
I'm sorry, that is a bad word.
Please don't be mad at me.
So hello world 1 to 4 gives us that, but that's not the only way of using it.
Here's another example.
Select substring hello world.
The same starting piece of data and this time all we give it is one number one index seven.
So if we count one, two, three, four, five, space is six seven is that w you might think it's only
going to give us that W But that's not true.
It gives us from that.
W on to the end.
So if you only pass it in one number rather than two, it starts from whatever that index is and just
goes to the end.
So let's try a couple out.
We'll do a select substring and we'll just do the hello world example that we just saw and we'll go
from 1 to 4.
So we already know the answer here.
So there's hell as you can see.
One, two, three, four.
We get hell.
So if we change that from let's do 3 to 8, we get low whorl.
So that's one way of using it.
And then the next way, as we saw, is if we just leave that second or that third argument off and we
just say, Hello, world comma three, it will go from the third character.
So one, two, three to the end.
So we get low world or in the case that I showed, if we do seven, we get world.
And there's one more way that we can use it, which is to actually give it a negative number, negative
index.
So what do you think will happen.
I give it negative three.
Hello world.
Well, the answer is that we get rld so it starts from the end of the string and it goes from one or
negative one excuse me to -2 to -3.
It includes all of them.
So rld if we try that give negative three, you can see we get rld and if we did negative seven it would
go one, two, three, four, five, six, seven to this o and we get o world.
So that's how we do it with just static data.
But now let's say we wanted to work with a column, so in our case let's take the example of title.
So let me just do a select star from actually let's just like title from books.
So we've got a variety of lengths of titles, right?
Short ones like White Noise, Coraline, long ones like what we talk about when we talk about love stories.
Let's say I wanted to just print out, I don't know, the first ten characters of every title.
Maybe you've probably seen that before when you're on a shop and if there's a long title, the whole
thing isn't printed out.
There's just a couple words and then dot, dot, dot or read more or something like that that you can
click on.
We'll do something similar.
So to limit this, what we could do, let's just take this as an example.
We can do a select substring, we'll give it the whole title, and then we only want from 0 to 10 or
from 1 to 10.
Excuse me, we could do that.
And this actually here's another error that I considered editing out, but I've decided not to because
it is worth taking a note of.
The problem is that it thinks that I'm still in a quote because I have this opening quote and this closing
quote.
But in between I have another quote, which is actually an apostrophe in this case.
So this is not going to work.
If I do this, I'll just exit out.
Just ignore that.
But I'll have to read you the whole line.
And rather than using single quotes, I'll use double quotes like this where I'm calling from selected
stories.
Just like that.
All right.
And then I'll get rid of this other quote I put at the end.
So that is important.
Just to take note of when we're working with strings, if you have a quotation mark in there, you've
got to work around it.
All right, so I do this and you can see it gives me where I'm.
Yes, this is ten characters, maybe isn't long enough, but we're just going to work with that.
So now to do it to every single title, it works just like we did with King Cat.
We're going to do a select substring title, comma, one, comma, ten from books, and that's it.
And there we go.
Now we have this ugly heading up here.
So we could do an as again as and let's just say short title like that.
Now we get this short title, blah, blah, blah.
The name say Norse myth, American G, a hologram.
Some of these actually work.
The circle.
There we go.
Just kid.
All right, so one other thing I'll show you.
There is a shortcut.
It's a very small shortcut, but you can type substrate sub SDR.
I don't use this all that often because I can really remember what the shortcut is because in some languages
they'll be substring.
Where it's Scooby est, there's some I've seen where it's SBS t it's just not clear which characters
get cut out because it's not just all vowels because we have a you still.
Anyways, so if you would like you can use this.
I can just show you if you replace that.
If we do select substring and just change a substring to be substring, it does the same exact thing.
But what I also want to show you is that we can combine contact and substring or any of the string operators
or the string functions you'll see.
So let's say I wanted to add a dot, dot, dot after this so that it looks like this short title, the
name say dot, dot, dot, rather than just an abrupt cut off, let's add a dot, dot, dot.
So hopefully you can identify what we need to do is first chop our string, use a substring 1 to 10
and then concatenate that with dot, dot, dot.
So I'll do this in a separate file.
I'll go ahead and make a new file and I'll just call it something silly, like book code dot SQL.
So we know we're going to do a select and there's going to be a king cat in there.
And what we're going to contest is the short title.
So again, this is wrong what I'm typing, but I just want to put a placeholder, the short title with
a dot dot dot from books so this won't do anything right now.
We need to replace this with how we actually achieve that short title and that's going to be with a
substring.
So in here we type substring and we want to put title in there oops comma and we want it to go from
1 to 10.
So this looks like a lot.
It's pretty ugly to format it a little nicer.
We can do this.
Depends on how intense you want to be about it.
I like to just do it like this here.
So substring is our first argument.
Comma, dot, dot, dot is our second one from books.
So if you look at it you can see the order.
Things are going to happen.
So substring is going to happen first for every title.
So that will be the name say or American G and that will be concatenated with dot, dot, dot, and
that will run for every single book in our database from books.
So if we save that, we could either source the file or just copy and paste it.
So let's do that now we'll do source book code dot SQL and there we go.
The name say dot, dot, dot, Nora Smith dot, dot, dot, dot, dot.
But you see, we get this really hideous title up here.
So let's put in as we'll call it as short title, save again, rerun it.
Now we get this much, much nicer short title, the name say dot, dot, dot, and so on.
All right.
So that's all there is to substring.
But the tricky thing, in my opinion, is just knowing that you can combine them.
So it takes a little bit of getting used to.
But again, the way it works is whatever is on the inside first will be evaluated, so substring will
happen first and then that will be concatenated with this.
So that's it.
We have a couple more and then we'll do some challenges.