1
00:00:00,120 --> 00:00:04,800
Next up in this onslaught of string functions is substring.

2
00:00:05,010 --> 00:00:09,560
So substring allows us to select individual parts or portions of a string.

3
00:00:09,570 --> 00:00:11,150
I only want the first character.

4
00:00:11,160 --> 00:00:15,180
I only want the middle ten characters or the last character or so on.

5
00:00:15,330 --> 00:00:17,400
So the syntax is similar to King Cat.

6
00:00:17,940 --> 00:00:18,930
Here's an example.

7
00:00:19,860 --> 00:00:23,400
In this case, we're not working with columns or tables or anything.

8
00:00:23,640 --> 00:00:25,180
We're just working with static data.

9
00:00:25,200 --> 00:00:26,010
Hello world.

10
00:00:26,460 --> 00:00:28,320
And with this will do select.

11
00:00:28,320 --> 00:00:33,750
So there's our select again and then substring our string function in parentheses.

12
00:00:34,200 --> 00:00:36,920
In this example, the first thing we do is tell it.

13
00:00:36,930 --> 00:00:38,760
Here is the string we want to work with.

14
00:00:38,760 --> 00:00:41,010
So that first thing is Hello World.

15
00:00:41,280 --> 00:00:48,030
And then what we're going to do is only print out the substring the portion from 1 to 4.

16
00:00:48,510 --> 00:00:55,230
So those are indices referring to characters and in my SQL they start at one.

17
00:00:55,230 --> 00:00:58,660
If you're familiar with other programming languages, they often start at zero.

18
00:00:58,680 --> 00:01:00,000
That's not the case here.

19
00:01:00,270 --> 00:01:02,430
So H is one.

20
00:01:02,430 --> 00:01:04,319
So we go from 1 to 4.

21
00:01:04,769 --> 00:01:09,480
So one, two, three, four, which gives us hell.

22
00:01:09,870 --> 00:01:11,430
I'm sorry, that is a bad word.

23
00:01:12,060 --> 00:01:13,050
Please don't be mad at me.

24
00:01:13,530 --> 00:01:18,420
So hello world 1 to 4 gives us that, but that's not the only way of using it.

25
00:01:19,560 --> 00:01:20,730
Here's another example.

26
00:01:21,180 --> 00:01:22,920
Select substring hello world.

27
00:01:22,920 --> 00:01:29,970
The same starting piece of data and this time all we give it is one number one index seven.

28
00:01:30,210 --> 00:01:39,120
So if we count one, two, three, four, five, space is six seven is that w you might think it's only

29
00:01:39,120 --> 00:01:41,880
going to give us that W But that's not true.

30
00:01:42,480 --> 00:01:43,740
It gives us from that.

31
00:01:43,740 --> 00:01:45,420
W on to the end.

32
00:01:45,420 --> 00:01:51,750
So if you only pass it in one number rather than two, it starts from whatever that index is and just

33
00:01:51,750 --> 00:01:52,800
goes to the end.

34
00:01:52,800 --> 00:01:54,210
So let's try a couple out.

35
00:01:54,600 --> 00:02:02,520
We'll do a select substring and we'll just do the hello world example that we just saw and we'll go

36
00:02:02,520 --> 00:02:03,540
from 1 to 4.

37
00:02:04,200 --> 00:02:05,760
So we already know the answer here.

38
00:02:06,840 --> 00:02:09,780
So there's hell as you can see.

39
00:02:10,139 --> 00:02:11,490
One, two, three, four.

40
00:02:11,490 --> 00:02:12,360
We get hell.

41
00:02:12,780 --> 00:02:21,030
So if we change that from let's do 3 to 8, we get low whorl.

42
00:02:21,420 --> 00:02:22,650
So that's one way of using it.

43
00:02:22,650 --> 00:02:27,540
And then the next way, as we saw, is if we just leave that second or that third argument off and we

44
00:02:27,540 --> 00:02:31,140
just say, Hello, world comma three, it will go from the third character.

45
00:02:31,140 --> 00:02:34,110
So one, two, three to the end.

46
00:02:34,710 --> 00:02:40,410
So we get low world or in the case that I showed, if we do seven, we get world.

47
00:02:41,700 --> 00:02:46,440
And there's one more way that we can use it, which is to actually give it a negative number, negative

48
00:02:46,440 --> 00:02:47,190
index.

49
00:02:47,550 --> 00:02:48,890
So what do you think will happen.

50
00:02:49,320 --> 00:02:50,580
I give it negative three.

51
00:02:51,390 --> 00:02:52,290
Hello world.

52
00:02:53,160 --> 00:02:59,910
Well, the answer is that we get rld so it starts from the end of the string and it goes from one or

53
00:02:59,910 --> 00:03:03,030
negative one excuse me to -2 to -3.

54
00:03:03,660 --> 00:03:05,010
It includes all of them.

55
00:03:05,100 --> 00:03:17,850
So rld if we try that give negative three, you can see we get rld and if we did negative seven it would

56
00:03:17,850 --> 00:03:24,480
go one, two, three, four, five, six, seven to this o and we get o world.

57
00:03:24,870 --> 00:03:27,240
So that's how we do it with just static data.

58
00:03:27,420 --> 00:03:34,590
But now let's say we wanted to work with a column, so in our case let's take the example of title.

59
00:03:34,920 --> 00:03:41,790
So let me just do a select star from actually let's just like title from books.

60
00:03:42,480 --> 00:03:45,480
So we've got a variety of lengths of titles, right?

61
00:03:45,480 --> 00:03:52,410
Short ones like White Noise, Coraline, long ones like what we talk about when we talk about love stories.

62
00:03:52,410 --> 00:03:58,140
Let's say I wanted to just print out, I don't know, the first ten characters of every title.

63
00:03:58,170 --> 00:04:02,490
Maybe you've probably seen that before when you're on a shop and if there's a long title, the whole

64
00:04:02,490 --> 00:04:03,450
thing isn't printed out.

65
00:04:03,450 --> 00:04:09,810
There's just a couple words and then dot, dot, dot or read more or something like that that you can

66
00:04:09,810 --> 00:04:10,440
click on.

67
00:04:10,440 --> 00:04:11,820
We'll do something similar.

68
00:04:12,240 --> 00:04:17,519
So to limit this, what we could do, let's just take this as an example.

69
00:04:19,079 --> 00:04:28,230
We can do a select substring, we'll give it the whole title, and then we only want from 0 to 10 or

70
00:04:28,230 --> 00:04:28,980
from 1 to 10.

71
00:04:28,980 --> 00:04:31,590
Excuse me, we could do that.

72
00:04:32,310 --> 00:04:38,490
And this actually here's another error that I considered editing out, but I've decided not to because

73
00:04:38,490 --> 00:04:40,800
it is worth taking a note of.

74
00:04:40,980 --> 00:04:47,250
The problem is that it thinks that I'm still in a quote because I have this opening quote and this closing

75
00:04:47,250 --> 00:04:47,640
quote.

76
00:04:47,640 --> 00:04:52,290
But in between I have another quote, which is actually an apostrophe in this case.

77
00:04:53,190 --> 00:04:55,050
So this is not going to work.

78
00:04:55,080 --> 00:04:57,720
If I do this, I'll just exit out.

79
00:04:57,720 --> 00:04:59,280
Just ignore that.

80
00:05:00,030 --> 00:05:02,370
But I'll have to read you the whole line.

81
00:05:02,370 --> 00:05:12,180
And rather than using single quotes, I'll use double quotes like this where I'm calling from selected

82
00:05:12,180 --> 00:05:12,930
stories.

83
00:05:15,030 --> 00:05:15,960
Just like that.

84
00:05:16,710 --> 00:05:17,160
All right.

85
00:05:17,160 --> 00:05:19,380
And then I'll get rid of this other quote I put at the end.

86
00:05:21,120 --> 00:05:22,110
So that is important.

87
00:05:22,110 --> 00:05:26,700
Just to take note of when we're working with strings, if you have a quotation mark in there, you've

88
00:05:26,700 --> 00:05:27,750
got to work around it.

89
00:05:28,440 --> 00:05:31,860
All right, so I do this and you can see it gives me where I'm.

90
00:05:32,190 --> 00:05:36,330
Yes, this is ten characters, maybe isn't long enough, but we're just going to work with that.

91
00:05:36,540 --> 00:05:41,730
So now to do it to every single title, it works just like we did with King Cat.

92
00:05:41,730 --> 00:05:53,310
We're going to do a select substring title, comma, one, comma, ten from books, and that's it.

93
00:05:54,900 --> 00:05:55,950
And there we go.

94
00:05:56,520 --> 00:05:58,560
Now we have this ugly heading up here.

95
00:05:58,560 --> 00:06:05,340
So we could do an as again as and let's just say short title like that.

96
00:06:08,590 --> 00:06:11,380
Now we get this short title, blah, blah, blah.

97
00:06:11,410 --> 00:06:16,460
The name say Norse myth, American G, a hologram.

98
00:06:16,480 --> 00:06:17,400
Some of these actually work.

99
00:06:17,410 --> 00:06:18,000
The circle.

100
00:06:18,010 --> 00:06:18,670
There we go.

101
00:06:18,700 --> 00:06:19,630
Just kid.

102
00:06:20,290 --> 00:06:22,960
All right, so one other thing I'll show you.

103
00:06:22,990 --> 00:06:23,950
There is a shortcut.

104
00:06:23,950 --> 00:06:29,380
It's a very small shortcut, but you can type substrate sub SDR.

105
00:06:29,800 --> 00:06:36,850
I don't use this all that often because I can really remember what the shortcut is because in some languages

106
00:06:36,850 --> 00:06:37,780
they'll be substring.

107
00:06:37,780 --> 00:06:45,670
Where it's Scooby est, there's some I've seen where it's SBS t it's just not clear which characters

108
00:06:45,670 --> 00:06:49,270
get cut out because it's not just all vowels because we have a you still.

109
00:06:50,080 --> 00:06:52,480
Anyways, so if you would like you can use this.

110
00:06:52,480 --> 00:06:55,030
I can just show you if you replace that.

111
00:06:55,300 --> 00:07:03,580
If we do select substring and just change a substring to be substring, it does the same exact thing.

112
00:07:04,480 --> 00:07:11,140
But what I also want to show you is that we can combine contact and substring or any of the string operators

113
00:07:11,140 --> 00:07:12,520
or the string functions you'll see.

114
00:07:13,060 --> 00:07:20,410
So let's say I wanted to add a dot, dot, dot after this so that it looks like this short title, the

115
00:07:20,410 --> 00:07:25,270
name say dot, dot, dot, rather than just an abrupt cut off, let's add a dot, dot, dot.

116
00:07:25,750 --> 00:07:32,470
So hopefully you can identify what we need to do is first chop our string, use a substring 1 to 10

117
00:07:32,470 --> 00:07:36,040
and then concatenate that with dot, dot, dot.

118
00:07:36,760 --> 00:07:38,650
So I'll do this in a separate file.

119
00:07:38,950 --> 00:07:45,160
I'll go ahead and make a new file and I'll just call it something silly, like book code dot SQL.

120
00:07:46,330 --> 00:07:50,170
So we know we're going to do a select and there's going to be a king cat in there.

121
00:07:50,320 --> 00:07:53,200
And what we're going to contest is the short title.

122
00:07:53,200 --> 00:07:58,480
So again, this is wrong what I'm typing, but I just want to put a placeholder, the short title with

123
00:07:58,480 --> 00:08:03,580
a dot dot dot from books so this won't do anything right now.

124
00:08:03,670 --> 00:08:09,670
We need to replace this with how we actually achieve that short title and that's going to be with a

125
00:08:09,670 --> 00:08:10,390
substring.

126
00:08:11,440 --> 00:08:23,470
So in here we type substring and we want to put title in there oops comma and we want it to go from

127
00:08:23,470 --> 00:08:24,550
1 to 10.

128
00:08:25,150 --> 00:08:26,170
So this looks like a lot.

129
00:08:26,170 --> 00:08:29,080
It's pretty ugly to format it a little nicer.

130
00:08:29,080 --> 00:08:30,220
We can do this.

131
00:08:31,090 --> 00:08:33,370
Depends on how intense you want to be about it.

132
00:08:33,520 --> 00:08:35,710
I like to just do it like this here.

133
00:08:35,860 --> 00:08:38,470
So substring is our first argument.

134
00:08:38,470 --> 00:08:43,210
Comma, dot, dot, dot is our second one from books.

135
00:08:43,360 --> 00:08:45,670
So if you look at it you can see the order.

136
00:08:45,700 --> 00:08:46,720
Things are going to happen.

137
00:08:46,720 --> 00:08:50,350
So substring is going to happen first for every title.

138
00:08:50,350 --> 00:08:57,760
So that will be the name say or American G and that will be concatenated with dot, dot, dot, and

139
00:08:57,760 --> 00:09:02,440
that will run for every single book in our database from books.

140
00:09:03,010 --> 00:09:08,050
So if we save that, we could either source the file or just copy and paste it.

141
00:09:08,050 --> 00:09:16,990
So let's do that now we'll do source book code dot SQL and there we go.

142
00:09:17,020 --> 00:09:21,220
The name say dot, dot, dot, Nora Smith dot, dot, dot, dot, dot.

143
00:09:21,700 --> 00:09:25,180
But you see, we get this really hideous title up here.

144
00:09:25,270 --> 00:09:35,260
So let's put in as we'll call it as short title, save again, rerun it.

145
00:09:36,460 --> 00:09:42,160
Now we get this much, much nicer short title, the name say dot, dot, dot, and so on.

146
00:09:42,730 --> 00:09:43,120
All right.

147
00:09:43,120 --> 00:09:45,250
So that's all there is to substring.

148
00:09:45,610 --> 00:09:49,960
But the tricky thing, in my opinion, is just knowing that you can combine them.

149
00:09:50,530 --> 00:09:52,270
So it takes a little bit of getting used to.

150
00:09:52,270 --> 00:09:57,790
But again, the way it works is whatever is on the inside first will be evaluated, so substring will

151
00:09:57,790 --> 00:10:01,270
happen first and then that will be concatenated with this.

152
00:10:01,270 --> 00:10:02,170
So that's it.

153
00:10:02,290 --> 00:10:04,660
We have a couple more and then we'll do some challenges.