The next string function we'll look at is called substring.
It takes a single larger string and returns a smaller portion of that string.
Maybe the first five characters, the last character, the middle ten characters, however.
However we want to use it, The point is to take a larger piece of text and then get a smaller piece
out of it or a substring.
So the docs here can be very useful if you're confused on this one.
It's not as straightforward as can necessarily.
But if you go to the string function's docs page, we're looking for for substring, it's alphabetized.
Or you just do a command f search substring.
There's a couple of different ways of using it.
You'll see here in their examples there's a piece of text and then a number or two numbers or from.
And for it can be a little overwhelming.
But we're going to start simply.
The first thing you have to know about substring is the first value you must pass.
It is the string that you want to sample or take a substring from.
And eventually that will be a column name.
But for now, let's work with a single string.
How about Hello World?
So that will always be the first value you provide a single piece of text or a column name.
This is called an argument.
The first argument in our case is Hello World.
And then we have two numbers in this example.
The first number is a starting position.
This is character one.
We're saying we want to start with a substring at character one H and we want to go for four characters.
So this second number is the length of our substring.
So this is the starting point and this is not the ending point, but rather the length.
So start at one and go to two to 3 to 4, meaning this substring right here, which is the word hell
in this case.
So let's try that select substring.
And then Hello World from one for four characters and we get hll.
If I instead had said start at one and go for six characters we get hello.
But there's also a space there.
You just can't tell.
Can we tell?
No, but there's a space right there.
Right.
This is one, two, three, four, five, six.
If I went for seven characters, we get the W, I could change the starting point to what if I want
to do elo?
Like, you know, every British person says hello that way.
I'm just kidding.
I know they don't.
But let's say that's how everyone says it in Britain.
ll0, how would I get that substring?
Well, I need to start at the E, which is position 212, and then I need to go.
For how many characters?
One character, two character, three, four characters.
And I have an equal sign that I didn't mean to put in there.
And we get Ello.
All right, So that's one way of using it.
But we can also provide just a single number.
And if we do that, it will be the starting point of our substring in this case.
Seven.
So one, two, three, four, five.
Don't forget the space counts.
So that's six, This is seven.
And then it tells my SQL, just go to the end.
If we don't provide a length as a second number, it will just start at that point and go until it hits
the end of the string.
So we get worlds.
Let's try that again.
Let's do it for real.
Start at index or position seven and it goes to the end.
But of course I could say go for two characters and we get, Whoa, whoa.
But if I leave that second length off, it goes as far as it can.
So if I wanted to instead do I don't know, Oh, world to the end, I would start with one, two, three,
four, five.
And go to the end and I get O world.
Now, all of this is so contrived.
You wouldn't do this the way that I'm doing it right now.
If I needed a world, I would just type that.
Why would I select a substring?
But of course, when we combine this with an existing table with rows, we can do dynamic sub strings,
which is where we're heading in just a moment.
So here's one more example.
We can actually use a negative starting point as well.
If I use a negative number, negative three, it's counting backwards from the end of the string.
So this is one, two, three, and then it goes forwards to the end.
I don't use this all that often, but if you ever needed like the last character in a string, just
the last one, and you don't know how long it is or anything, you can do negative one.
Because whatever this is, negative one is always going to be starting from the end of that string.
That last character, negative two would be the second to last character and then going forward.
And if I only wanted the second to last character, I would say go for one character, right?
So start negative two.
That would be negative one, negative two, and then count forwards for one character, aka Don't move
and give me that.
L So this is all silly stuff.
Don't worry if you're a little confused because the way that we usually use this is with some real data.
So let's go back to our books Data select, I don't know, title from books and these are kind of long.
Some of them write what we talk about when we talk about love stories.
What if we only wanted to work with the first 15 characters and abbreviate the rest?
Or not abbreviate, but but just ignore the rest?
It's just to make things easier.
Well, I would do something like this.
Select.
And then substring and then title.
And then we want to start at that first character and we'll move for 15 characters from books.
So start at that first one, go for 15 and we get things like The Namesake.
But then what we talk about because we said, Start this first character and allow us to take a substring
for 15 characters and the rest just doesn't matter.
So you might not want to do this, but this is an example of where you could use substring.
You also should know that there's a shorter version.
It's identical.
It's just sub str.
If you don't want to type substring, you'll see this on the docs to its right above it says se sub
str is a synonym for the full substring.
They work exactly the same.
It's just a little shorter.
So that's the basics.
Let's try one more example.
Let's see.
Select start from books.
I don't know.
The titles are the longest, but let's just try getting.
How about the first character of the author's last name for every book?
So that would be simple enough.
Select substring.
How do we get the first character?
Well, we're going to say author L name.
We want starting at character one move for one character.
So just give us the first character from books and we see.
LGL Why don't we also put the full author last name afterwards?
So author LL name and now we can see that it's working the here he was just shortened to L Garman with
G Eggers.
Is E all right?
We could also rename this if we wanted to.
We could call it something like substring as.
Initial.
And there we are.
Initial WL author ll name was Lahiri.
So I'm going to show you some more examples of substring where we can combine it with content and get
extra fancy in the next video.