1
00:00:00,090 --> 00:00:02,790
The next string function we'll look at is called substring.

2
00:00:02,820 --> 00:00:08,119
It takes a single larger string and returns a smaller portion of that string.

3
00:00:08,130 --> 00:00:12,840
Maybe the first five characters, the last character, the middle ten characters, however.

4
00:00:12,940 --> 00:00:18,840
However we want to use it, The point is to take a larger piece of text and then get a smaller piece

5
00:00:18,840 --> 00:00:20,730
out of it or a substring.

6
00:00:21,360 --> 00:00:24,720
So the docs here can be very useful if you're confused on this one.

7
00:00:24,930 --> 00:00:27,570
It's not as straightforward as can necessarily.

8
00:00:27,750 --> 00:00:33,900
But if you go to the string function's docs page, we're looking for for substring, it's alphabetized.

9
00:00:33,900 --> 00:00:37,350
Or you just do a command f search substring.

10
00:00:37,830 --> 00:00:39,900
There's a couple of different ways of using it.

11
00:00:39,930 --> 00:00:45,960
You'll see here in their examples there's a piece of text and then a number or two numbers or from.

12
00:00:45,960 --> 00:00:47,850
And for it can be a little overwhelming.

13
00:00:47,850 --> 00:00:49,440
But we're going to start simply.

14
00:00:49,500 --> 00:00:54,930
The first thing you have to know about substring is the first value you must pass.

15
00:00:54,930 --> 00:00:59,340
It is the string that you want to sample or take a substring from.

16
00:00:59,520 --> 00:01:01,480
And eventually that will be a column name.

17
00:01:01,500 --> 00:01:03,940
But for now, let's work with a single string.

18
00:01:03,960 --> 00:01:05,540
How about Hello World?

19
00:01:05,550 --> 00:01:10,080
So that will always be the first value you provide a single piece of text or a column name.

20
00:01:10,110 --> 00:01:11,400
This is called an argument.

21
00:01:11,430 --> 00:01:13,980
The first argument in our case is Hello World.

22
00:01:14,310 --> 00:01:17,360
And then we have two numbers in this example.

23
00:01:17,370 --> 00:01:20,190
The first number is a starting position.

24
00:01:20,460 --> 00:01:21,870
This is character one.

25
00:01:21,870 --> 00:01:28,650
We're saying we want to start with a substring at character one H and we want to go for four characters.

26
00:01:28,650 --> 00:01:33,150
So this second number is the length of our substring.

27
00:01:33,180 --> 00:01:38,010
So this is the starting point and this is not the ending point, but rather the length.

28
00:01:38,250 --> 00:01:46,140
So start at one and go to two to 3 to 4, meaning this substring right here, which is the word hell

29
00:01:46,140 --> 00:01:46,860
in this case.

30
00:01:46,860 --> 00:01:51,030
So let's try that select substring.

31
00:01:52,070 --> 00:02:00,620
And then Hello World from one for four characters and we get hll.

32
00:02:00,650 --> 00:02:06,500
If I instead had said start at one and go for six characters we get hello.

33
00:02:06,500 --> 00:02:07,820
But there's also a space there.

34
00:02:07,820 --> 00:02:09,020
You just can't tell.

35
00:02:09,050 --> 00:02:09,889
Can we tell?

36
00:02:09,889 --> 00:02:11,780
No, but there's a space right there.

37
00:02:11,780 --> 00:02:12,170
Right.

38
00:02:12,170 --> 00:02:15,350
This is one, two, three, four, five, six.

39
00:02:15,350 --> 00:02:20,840
If I went for seven characters, we get the W, I could change the starting point to what if I want

40
00:02:20,840 --> 00:02:22,220
to do elo?

41
00:02:22,250 --> 00:02:26,360
Like, you know, every British person says hello that way.

42
00:02:27,050 --> 00:02:27,680
I'm just kidding.

43
00:02:27,680 --> 00:02:28,250
I know they don't.

44
00:02:28,250 --> 00:02:30,500
But let's say that's how everyone says it in Britain.

45
00:02:30,880 --> 00:02:34,490
ll0, how would I get that substring?

46
00:02:34,970 --> 00:02:42,110
Well, I need to start at the E, which is position 212, and then I need to go.

47
00:02:42,110 --> 00:02:43,070
For how many characters?

48
00:02:43,070 --> 00:02:46,310
One character, two character, three, four characters.

49
00:02:47,330 --> 00:02:49,640
And I have an equal sign that I didn't mean to put in there.

50
00:02:49,640 --> 00:02:51,140
And we get Ello.

51
00:02:51,590 --> 00:02:53,510
All right, So that's one way of using it.

52
00:02:53,510 --> 00:02:56,750
But we can also provide just a single number.

53
00:02:56,750 --> 00:03:01,070
And if we do that, it will be the starting point of our substring in this case.

54
00:03:01,070 --> 00:03:01,940
Seven.

55
00:03:01,940 --> 00:03:04,460
So one, two, three, four, five.

56
00:03:04,490 --> 00:03:05,780
Don't forget the space counts.

57
00:03:05,780 --> 00:03:07,970
So that's six, This is seven.

58
00:03:07,970 --> 00:03:11,000
And then it tells my SQL, just go to the end.

59
00:03:11,000 --> 00:03:16,070
If we don't provide a length as a second number, it will just start at that point and go until it hits

60
00:03:16,070 --> 00:03:17,090
the end of the string.

61
00:03:17,090 --> 00:03:18,920
So we get worlds.

62
00:03:19,280 --> 00:03:20,510
Let's try that again.

63
00:03:20,510 --> 00:03:21,680
Let's do it for real.

64
00:03:21,680 --> 00:03:25,490
Start at index or position seven and it goes to the end.

65
00:03:25,490 --> 00:03:29,750
But of course I could say go for two characters and we get, Whoa, whoa.

66
00:03:30,050 --> 00:03:33,920
But if I leave that second length off, it goes as far as it can.

67
00:03:34,310 --> 00:03:42,440
So if I wanted to instead do I don't know, Oh, world to the end, I would start with one, two, three,

68
00:03:42,440 --> 00:03:43,820
four, five.

69
00:03:44,870 --> 00:03:48,200
And go to the end and I get O world.

70
00:03:48,440 --> 00:03:49,970
Now, all of this is so contrived.

71
00:03:49,970 --> 00:03:53,100
You wouldn't do this the way that I'm doing it right now.

72
00:03:53,120 --> 00:03:55,720
If I needed a world, I would just type that.

73
00:03:55,730 --> 00:03:57,580
Why would I select a substring?

74
00:03:57,590 --> 00:04:03,710
But of course, when we combine this with an existing table with rows, we can do dynamic sub strings,

75
00:04:03,710 --> 00:04:05,330
which is where we're heading in just a moment.

76
00:04:05,900 --> 00:04:07,490
So here's one more example.

77
00:04:07,490 --> 00:04:10,670
We can actually use a negative starting point as well.

78
00:04:10,700 --> 00:04:16,190
If I use a negative number, negative three, it's counting backwards from the end of the string.

79
00:04:16,190 --> 00:04:21,380
So this is one, two, three, and then it goes forwards to the end.

80
00:04:21,470 --> 00:04:26,060
I don't use this all that often, but if you ever needed like the last character in a string, just

81
00:04:26,060 --> 00:04:29,780
the last one, and you don't know how long it is or anything, you can do negative one.

82
00:04:30,050 --> 00:04:36,200
Because whatever this is, negative one is always going to be starting from the end of that string.

83
00:04:36,200 --> 00:04:42,380
That last character, negative two would be the second to last character and then going forward.

84
00:04:42,530 --> 00:04:48,740
And if I only wanted the second to last character, I would say go for one character, right?

85
00:04:48,950 --> 00:04:50,360
So start negative two.

86
00:04:50,390 --> 00:04:56,660
That would be negative one, negative two, and then count forwards for one character, aka Don't move

87
00:04:56,660 --> 00:04:57,500
and give me that.

88
00:04:57,500 --> 00:05:00,020
L So this is all silly stuff.

89
00:05:00,020 --> 00:05:05,810
Don't worry if you're a little confused because the way that we usually use this is with some real data.

90
00:05:05,810 --> 00:05:13,520
So let's go back to our books Data select, I don't know, title from books and these are kind of long.

91
00:05:13,520 --> 00:05:17,090
Some of them write what we talk about when we talk about love stories.

92
00:05:17,090 --> 00:05:21,170
What if we only wanted to work with the first 15 characters and abbreviate the rest?

93
00:05:21,170 --> 00:05:23,840
Or not abbreviate, but but just ignore the rest?

94
00:05:23,840 --> 00:05:25,310
It's just to make things easier.

95
00:05:25,310 --> 00:05:26,810
Well, I would do something like this.

96
00:05:26,810 --> 00:05:27,650
Select.

97
00:05:28,540 --> 00:05:32,500
And then substring and then title.

98
00:05:33,040 --> 00:05:39,460
And then we want to start at that first character and we'll move for 15 characters from books.

99
00:05:39,610 --> 00:05:44,350
So start at that first one, go for 15 and we get things like The Namesake.

100
00:05:44,350 --> 00:05:50,440
But then what we talk about because we said, Start this first character and allow us to take a substring

101
00:05:50,440 --> 00:05:53,380
for 15 characters and the rest just doesn't matter.

102
00:05:53,860 --> 00:05:58,700
So you might not want to do this, but this is an example of where you could use substring.

103
00:05:58,720 --> 00:06:01,630
You also should know that there's a shorter version.

104
00:06:01,630 --> 00:06:02,710
It's identical.

105
00:06:02,740 --> 00:06:05,140
It's just sub str.

106
00:06:05,140 --> 00:06:10,360
If you don't want to type substring, you'll see this on the docs to its right above it says se sub

107
00:06:10,390 --> 00:06:13,450
str is a synonym for the full substring.

108
00:06:13,450 --> 00:06:15,220
They work exactly the same.

109
00:06:15,220 --> 00:06:16,720
It's just a little shorter.

110
00:06:16,990 --> 00:06:18,100
So that's the basics.

111
00:06:18,100 --> 00:06:19,900
Let's try one more example.

112
00:06:20,380 --> 00:06:20,820
Let's see.

113
00:06:20,830 --> 00:06:22,300
Select start from books.

114
00:06:22,570 --> 00:06:23,050
I don't know.

115
00:06:23,050 --> 00:06:26,230
The titles are the longest, but let's just try getting.

116
00:06:26,230 --> 00:06:34,560
How about the first character of the author's last name for every book?

117
00:06:34,570 --> 00:06:36,580
So that would be simple enough.

118
00:06:37,030 --> 00:06:38,920
Select substring.

119
00:06:38,920 --> 00:06:40,660
How do we get the first character?

120
00:06:40,660 --> 00:06:43,780
Well, we're going to say author L name.

121
00:06:45,800 --> 00:06:49,730
We want starting at character one move for one character.

122
00:06:49,730 --> 00:06:54,440
So just give us the first character from books and we see.

123
00:06:54,470 --> 00:06:59,840
LGL Why don't we also put the full author last name afterwards?

124
00:06:59,840 --> 00:07:07,220
So author LL name and now we can see that it's working the here he was just shortened to L Garman with

125
00:07:07,220 --> 00:07:08,660
G Eggers.

126
00:07:08,660 --> 00:07:10,430
Is E all right?

127
00:07:10,430 --> 00:07:13,250
We could also rename this if we wanted to.

128
00:07:13,250 --> 00:07:17,750
We could call it something like substring as.

129
00:07:19,630 --> 00:07:20,500
Initial.

130
00:07:21,460 --> 00:07:22,180
And there we are.

131
00:07:22,210 --> 00:07:25,360
Initial WL author ll name was Lahiri.

132
00:07:25,660 --> 00:07:30,310
So I'm going to show you some more examples of substring where we can combine it with content and get

133
00:07:30,310 --> 00:07:31,870
extra fancy in the next video.