1
00:00:00,120 --> 00:00:07,110
In this video, we're going to get extra fancy and combine both can cat and substring together and use

2
00:00:07,110 --> 00:00:10,500
them in conjunction to do something like this right here.

3
00:00:10,830 --> 00:00:17,910
A shortened version of the title, which is just a substring concatenated with dot, dot, dot at the

4
00:00:17,910 --> 00:00:18,420
end.

5
00:00:18,600 --> 00:00:21,450
So we know how to do both of those pieces individually.

6
00:00:21,480 --> 00:00:24,060
Let's say I want to get this substring of a title.

7
00:00:24,060 --> 00:00:25,620
Well, how many characters is this?

8
00:00:25,650 --> 00:00:30,790
One, two, three, four, five, six, seven, eight, nine, ten, I believe.

9
00:00:30,810 --> 00:00:32,880
So we'll get the first ten characters.

10
00:00:32,880 --> 00:00:36,980
We can start there and I'll do it over here for now.

11
00:00:36,990 --> 00:00:41,370
Just in my interactive shell select sub.

12
00:00:41,520 --> 00:00:43,740
Let's do the short version str.

13
00:00:43,770 --> 00:00:45,780
Remember, either one works.

14
00:00:46,740 --> 00:00:48,150
And this is the title.

15
00:00:48,600 --> 00:00:54,240
I want to go from character one for ten characters from books, and let's see if that works.

16
00:00:54,240 --> 00:00:58,560
We get the name, say, Norse myth, American G.

17
00:00:59,400 --> 00:01:00,060
Okay.

18
00:01:00,210 --> 00:01:05,880
But what I actually want to do is also add the dot, dot, dot there to make it clear that this is a

19
00:01:05,880 --> 00:01:06,620
shortened version.

20
00:01:06,630 --> 00:01:09,900
It continues on and we're looking at the first ten characters.

21
00:01:10,560 --> 00:01:14,970
So what I want to do is concatenate this with three dots.

22
00:01:14,970 --> 00:01:16,530
So how do I do that?

23
00:01:16,680 --> 00:01:19,470
Well, it's easier if we work in a separate file.

24
00:01:19,470 --> 00:01:24,960
So you could work in any old SQL file you want, and then you could just source that file.

25
00:01:25,560 --> 00:01:26,940
That would be one option.

26
00:01:27,510 --> 00:01:34,140
Or we can work within one of our editors, which I think is what is easiest for most of my students.

27
00:01:34,260 --> 00:01:41,010
So instead of selecting just the substring like that, I'm going to select the cat.

28
00:01:43,210 --> 00:01:45,550
Of this substring.

29
00:01:47,160 --> 00:01:48,060
Comma.

30
00:01:48,180 --> 00:01:49,440
Dot, dot, dot.

31
00:01:50,810 --> 00:01:52,130
From books.

32
00:01:52,670 --> 00:01:59,660
What's happening here is that this will run first and this will be replaced with something like American

33
00:01:59,660 --> 00:02:02,870
GI or whatever one of those sub strings was.

34
00:02:02,870 --> 00:02:07,010
And then that will be concatenated with the dot, dot, dot.

35
00:02:07,740 --> 00:02:10,120
So I can run it from within here if I want.

36
00:02:10,139 --> 00:02:12,750
Let's just run this and then we go.

37
00:02:12,750 --> 00:02:13,830
We see our results.

38
00:02:13,830 --> 00:02:16,380
The name said dot, dot, dot Norse myth.

39
00:02:16,410 --> 00:02:17,360
Dot, dot, dot.

40
00:02:18,120 --> 00:02:19,740
I'll just run it over here.

41
00:02:20,790 --> 00:02:21,690
Same thing.

42
00:02:21,690 --> 00:02:28,980
Although if we look at my initial screenshot, I renamed it to be short title, so I probably would

43
00:02:28,980 --> 00:02:33,310
do that as well as short title from books.

44
00:02:33,330 --> 00:02:34,530
Let's run it again.

45
00:02:35,010 --> 00:02:35,760
And there we are.

46
00:02:35,790 --> 00:02:37,410
That's much more palatable.

47
00:02:38,040 --> 00:02:39,790
So there's a couple of notes here.

48
00:02:39,810 --> 00:02:45,840
One is make sure you're comfortable with this concept of or that you at least can explain what's happening

49
00:02:45,870 --> 00:02:50,280
of passing through a function result to another function.

50
00:02:50,280 --> 00:02:55,680
It's a little weird if you're not familiar with this, but we've got nested stuff and the innermost

51
00:02:55,680 --> 00:03:01,490
thing will be evaluated first for each row and then that will be passed to concatenate.

52
00:03:01,710 --> 00:03:06,060
But you can also think of it and you can reformat it to look like this.

53
00:03:06,060 --> 00:03:14,220
If it helps you, you can indent this like that right there and then do something like that.

54
00:03:14,730 --> 00:03:23,040
So it's not exactly perfectly formatted, but here we're basically saying can cut these two things together

55
00:03:23,040 --> 00:03:23,220
here.

56
00:03:23,220 --> 00:03:24,480
Let's do it like this.

57
00:03:25,470 --> 00:03:34,500
Can cat these two things this with this and call it short title and if it helps you understand how it

58
00:03:34,500 --> 00:03:39,630
works by having it nested and having it indented, by all means do that for longer queries.

59
00:03:39,630 --> 00:03:41,390
That is definitely worth doing.

60
00:03:41,400 --> 00:03:45,460
This one's not too long, but still, hopefully you are able to understand how it works.

61
00:03:45,480 --> 00:03:47,370
Now let's try one more example.

62
00:03:47,400 --> 00:03:51,150
Why don't we create a column that is an author's initials like this?

63
00:03:51,930 --> 00:03:55,140
Jhumpa Lahiri would be J period.

64
00:03:55,170 --> 00:03:56,580
L Period.

65
00:03:56,970 --> 00:03:58,730
So how am I going to do that?

66
00:03:58,740 --> 00:04:00,070
Well, how do I get J.

67
00:04:00,090 --> 00:04:01,080
Let's start there.

68
00:04:01,080 --> 00:04:03,960
We need to select the substring.

69
00:04:05,590 --> 00:04:07,120
Of author.

70
00:04:07,390 --> 00:04:10,390
F name for first name starting at character.

71
00:04:10,390 --> 00:04:12,640
One for one character.

72
00:04:12,780 --> 00:04:13,120
Right.

73
00:04:13,150 --> 00:04:18,760
And that should give us the first initial and then let's do the same thing for last name.

74
00:04:19,300 --> 00:04:26,710
So that would be author L name starting at character one, moving forward for one character from books.

75
00:04:27,370 --> 00:04:29,980
And I guess we can just run this one line.

76
00:04:29,980 --> 00:04:34,600
If I'm in the workbench, remember I click the second lightning bolt and it only runs where my cursor

77
00:04:34,600 --> 00:04:35,020
is.

78
00:04:35,770 --> 00:04:36,820
So we see J.

79
00:04:36,820 --> 00:04:38,320
L That's Jhumpa Lahiri.

80
00:04:38,320 --> 00:04:40,000
Neil Gaiman is and G.

81
00:04:40,090 --> 00:04:42,910
You could verify this by Just Remember the order.

82
00:04:42,910 --> 00:04:45,670
So that's getting us those two initials.

83
00:04:45,670 --> 00:04:51,970
But I want them to be combined so I can use concatenate, I can concatenate these two things.

84
00:04:52,420 --> 00:04:59,230
So I think it will be easier if I do this on a separate row I'm going to contact.

85
00:05:00,070 --> 00:05:04,690
This whole thing, comma, this whole thing.

86
00:05:05,080 --> 00:05:06,880
And now if I run this line.

87
00:05:08,180 --> 00:05:10,130
We get them smushed together.

88
00:05:10,130 --> 00:05:11,540
J.L. and G.

89
00:05:11,540 --> 00:05:12,050
And G.

90
00:05:12,050 --> 00:05:14,270
Jld for Dave Eggers.

91
00:05:14,630 --> 00:05:15,840
Now we're almost there.

92
00:05:15,860 --> 00:05:21,230
I want to add a period before and after, and remember, with Kit-Kat I can just put any old string

93
00:05:21,230 --> 00:05:22,010
I want in there.

94
00:05:22,010 --> 00:05:28,760
So there's a period between the F name, initial period, last name, initial, and then one more period

95
00:05:28,760 --> 00:05:29,570
at the end.

96
00:05:30,400 --> 00:05:32,170
Just make sure I put it in quotes.

97
00:05:32,410 --> 00:05:34,000
So let's try running this.

98
00:05:35,370 --> 00:05:36,720
And there we are.

99
00:05:36,750 --> 00:05:39,510
We see J or J dot, l dot.

100
00:05:39,780 --> 00:05:43,020
And then I could even rename this whole thing as.

101
00:05:43,770 --> 00:05:46,830
How about initial or author initials?

102
00:05:48,330 --> 00:05:49,350
Run it again.

103
00:05:51,180 --> 00:05:56,130
And my output here we see it's really hard to see the name author, the initials up there.

104
00:05:56,130 --> 00:05:57,960
Thank you for that SQL workbench.

105
00:05:58,290 --> 00:05:59,520
Let's try it over here.

106
00:06:00,300 --> 00:06:00,960
There we go.

107
00:06:00,960 --> 00:06:02,250
Author initials.

108
00:06:02,250 --> 00:06:05,910
So we're combining concatenation with sub strings.

109
00:06:05,910 --> 00:06:07,080
Kind of cool stuff.

110
00:06:07,080 --> 00:06:09,030
Also possibly confusing.

111
00:06:09,480 --> 00:06:13,380
Just take your time trying to walk through it and let me format these to look a little bit nicer.

112
00:06:13,710 --> 00:06:17,880
Here's a reformatted version so it's just slightly easier to read.

113
00:06:17,880 --> 00:06:20,340
You can see that this is going to be done first.

114
00:06:20,340 --> 00:06:23,760
That will be concatenated with the dot, dot, dot.

115
00:06:23,790 --> 00:06:29,370
We're renaming that whole column that we're creating from books and here, same deal, right?

116
00:06:29,370 --> 00:06:32,070
This will happen and this will happen.

117
00:06:32,070 --> 00:06:35,940
Those will be replaced with some letter and some other letter.

118
00:06:35,940 --> 00:06:41,430
And then they're concatenated with a dot and another dot and we call the result author initials.

119
00:06:42,060 --> 00:06:45,890
So again, confusing, but hopefully you can take your time and step through it.

120
00:06:45,900 --> 00:06:49,500
Next up, we're going to move on to some other built in string functions.