Next up in this onslaught of string functions is substring.
So substring allows us to select individual part portions of a string.
I only want the first character.
I only want the middle 10 characters or the last character so on.
So the syntax is similar.
Chicken 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 selector again and then substring our string function in parentheses.
In this example the first thing we do is tell it here's 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 ask you Will they start at 1 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 to 1 2 3 4 which gives us hell.
I'm sorry that is a bad word to me.
So hello world one before it 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 we give it is 1 number 1
index 7.
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 in one number rather than 2 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 it will go from
1 to 4.
So we already know the answer here.
Hope there's hell as you can see.
One two three four.
We get help.
So if we change that from let's do three to eight we get a low Oreille.
So that's one way of using it.
And then the next way as we saw is if we just leave that second or third argument off and we just say
hello world comet 3.
It will go from the third character or one to three to the end.
So we get low world or in the case that I showed you 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 3.
Hello world.
Well if the answer to that we get RLT So it starts from the end of the string and it goes from one negative
one excuse me to negative 2 to negative 3 and includes all of them.
So R L D.
We try that give negative three.
You can see we get R L D and if we did negative 7 would go 1 2 3 4 5 6 7 to this.
Oh and we get 0 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 that's just like title from books.
So we've got a variety of like some 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 10 characters of every title maybe.
You know 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 you know a couple words and then dot dot dot or read more something like that that you
can click on.
We'll do something similar.
So to limit this what we do let's just take this as an example.
We can do a select substring will 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 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 and just ignore that.
But I 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 you're working with strings.
If you have a quotation mark in there you've got to work around it.
So I do this and you can see it give me where I'm.
Yes this is 10 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 comment 10 from books and that's it.
And there we go.
Now we have this ugly heading up here so we could do it as again as in let's just say short title like
that.
Now we get this short title blah blah blah.
The names say North Smith American GI a hologram.
Some of these actually work in a circle.
There we go.
Just good.
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.
Su B SDR.
I don't use this all that often because I can really remember what the short cut is because in some
languages there'll be substring where it's SU b s t.
There's some I've seen s b s t.
It's just not clear which characters get cut out because it's not just all vowels because we have a
use still anyway so if you would like you can use this I can just show you if you replace that would
you select substring and just change substring to be substrata it does the same exact thing.
But what I also want to show you is that we can combine concat 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 it looks like this short title the name say dot dot dot rather than just an abrupt cut off at a dot
dot dot.
So hopefully you can identify what we need to do is first chop our string.
Use a substring 9:59 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 code that as well.
So we know we're going to do is select and there's going to be a king cat in there.
And what we're going to concat 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 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.
The 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 do source book code data as well.
And there we go.
The name say dot dot dot Norse myth dot dot dot dot dot dot dot.
But you see if we get this really hideous title appear so let's put in as we'll call it as short a 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's 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.