1
00:00:00,150 --> 00:00:03,960
The next built in string function that we'll work with is called replace.

2
00:00:04,230 --> 00:00:08,039
And what it does is it replaces parts of a string.

3
00:00:08,580 --> 00:00:14,130
So if you wanted to get rid of a certain occurrence of a character or you wanted to replace all spaces

4
00:00:14,130 --> 00:00:19,710
with commas or something like that, you could use replace and the syntax looks like this.

5
00:00:20,010 --> 00:00:23,640
Here's an example where we have the string hello world.

6
00:00:23,970 --> 00:00:25,800
And you know what?

7
00:00:25,800 --> 00:00:27,510
I just realized this again.

8
00:00:27,630 --> 00:00:29,880
That's a bad word right there.

9
00:00:30,540 --> 00:00:39,000
So if we wanted to get rid of that, we could use replace helloworld comma, what we're trying to remove

10
00:00:39,270 --> 00:00:41,820
and then what we want to replace it with.

11
00:00:42,390 --> 00:00:44,550
And it will do that to all occurrences.

12
00:00:44,550 --> 00:00:47,190
In this case, Hal only occurs once.

13
00:00:47,520 --> 00:00:49,410
So that's what our output looks like.

14
00:00:50,040 --> 00:00:52,830
Percent Star Octave Thorpe.

15
00:00:52,860 --> 00:00:54,240
Oh, what's this thing called?

16
00:00:54,240 --> 00:00:55,530
There's an actual name for it.

17
00:00:55,530 --> 00:00:56,640
The at symbol.

18
00:00:56,640 --> 00:00:59,970
But there's a real name and then O World.

19
00:01:00,270 --> 00:01:05,940
So if I copy that over and just run it now, you can see that's the output that we get.

20
00:01:06,810 --> 00:01:17,370
We could also do something like Replace Hello World and let's just replace all L's with sevens.

21
00:01:17,370 --> 00:01:18,090
Why not?

22
00:01:18,870 --> 00:01:20,550
And that's what we would need to do there.

23
00:01:22,050 --> 00:01:27,450
And then we get hello world but with sevens instead of L's, you know, we could do the same thing just

24
00:01:27,450 --> 00:01:32,760
to show you one more with O's and replace them with zero.

25
00:01:34,650 --> 00:01:37,560
Now, one thing that is important to to pay attention to.

26
00:01:38,400 --> 00:01:45,660
Let's say I have hello here with a capital oh.

27
00:01:45,690 --> 00:01:50,400
And hello here with a lowercase o or excuse me, world with lowercase.

28
00:01:50,400 --> 00:01:53,510
So the idea is just I have upper and lowercase o's in here.

29
00:01:53,520 --> 00:01:59,580
If I try and replace O and let's say just want to replace it with, let's do an asterisk.

30
00:02:00,120 --> 00:02:01,410
What do you think will happen?

31
00:02:03,680 --> 00:02:06,910
And the answer is it only replaces exactly this character.

32
00:02:06,910 --> 00:02:08,620
So it is case sensitive.

33
00:02:08,979 --> 00:02:10,860
So it didn't touch this capital.

34
00:02:10,870 --> 00:02:11,590
Oh, here.

35
00:02:12,160 --> 00:02:13,690
So that's one way to use replace.

36
00:02:13,930 --> 00:02:16,960
The other way that I mentioned is that it can replace multiple things.

37
00:02:17,110 --> 00:02:23,890
So here's a silly example where I have a string cheese space, bread, space, coffee, space, milk,

38
00:02:23,890 --> 00:02:26,170
maybe it's a grocery list or something.

39
00:02:26,650 --> 00:02:33,430
And I want to replace all spaces with the string and well, space and space.

40
00:02:33,550 --> 00:02:37,750
Basically the output will be cheese and bread and coffee and milk.

41
00:02:37,780 --> 00:02:40,060
It inserted that right there.

42
00:02:40,480 --> 00:02:41,890
Here, here and here.

43
00:02:41,890 --> 00:02:43,210
Every time there's a space.

44
00:02:44,080 --> 00:02:48,640
So if I try running that, you can see it works.

45
00:02:48,640 --> 00:02:50,290
Cheese and bread and coffee and milk.

46
00:02:50,710 --> 00:02:53,440
So now let's do something with the book data we have.

47
00:02:53,530 --> 00:02:56,950
We'll do something really simple and similar to what we just did.

48
00:02:56,970 --> 00:03:02,800
Let replace all ease with threes and we'll just do lowercase e.

49
00:03:02,800 --> 00:03:09,980
So any time there's a lowercase e like namesake, North American will replace it with a three.

50
00:03:10,000 --> 00:03:13,960
So to do that is a select replace and don't ask why.

51
00:03:14,110 --> 00:03:18,190
It's not really a real world example, but select replace.

52
00:03:18,400 --> 00:03:22,090
We want to do title and then what are we trying to replace?

53
00:03:22,120 --> 00:03:25,030
Lowercase is what do we want to replace them with?

54
00:03:25,450 --> 00:03:26,080
Three.

55
00:03:26,860 --> 00:03:27,460
And then what?

56
00:03:27,490 --> 00:03:30,010
Table from books.

57
00:03:30,370 --> 00:03:31,750
And we could get more specific.

58
00:03:31,750 --> 00:03:33,550
But let's just do from books.

59
00:03:35,640 --> 00:03:41,220
And you can see we get the namesake with three threes and all the way down.

60
00:03:41,790 --> 00:03:45,510
Consider the Lobster Cannery Row and so on.

61
00:03:45,520 --> 00:03:50,940
And that's pretty much it to using replace a bit of a bonus at the end here is that I'll show you we

62
00:03:50,940 --> 00:03:54,330
can combine replace with other functions like we've already seen.

63
00:03:54,330 --> 00:04:00,000
We use canned cat and substring together so we could try the same thing by using replace and canned

64
00:04:00,000 --> 00:04:01,260
cat for instance.

65
00:04:01,260 --> 00:04:07,800
So I could do something like if I work in this book code, I will comment this out, which I haven't

66
00:04:07,800 --> 00:04:08,940
shown how to do yet.

67
00:04:08,970 --> 00:04:09,990
Highlight it.

68
00:04:09,990 --> 00:04:12,960
And on a mac it's command slash on a PC.

69
00:04:12,960 --> 00:04:15,390
Believe it is control slash.

70
00:04:17,040 --> 00:04:20,640
So I'm going to comment it out, which means it just won't run it here so I can see it.

71
00:04:20,640 --> 00:04:25,980
But SQL treats it or my SQL treats it as if there's nothing there when it runs it.

72
00:04:26,280 --> 00:04:29,700
So what I want to do now, I want to combine, replace.

73
00:04:29,700 --> 00:04:36,210
So what I'm going to do is select and I'm just going to fill in some rough blanks here.

74
00:04:36,210 --> 00:04:40,020
So we have replace and then we're going to have from books.

75
00:04:41,310 --> 00:04:44,010
But what we want to do is not just replace things.

76
00:04:44,010 --> 00:04:47,580
I'll do the same thing where we replace all e.s with threes.

77
00:04:47,580 --> 00:04:49,890
So that's replaced title.

78
00:04:51,920 --> 00:04:55,550
The lowercase e with three like that.

79
00:04:55,910 --> 00:05:01,480
But I'm only going to take the first ten characters like we did with Substring.

80
00:05:01,490 --> 00:05:10,070
So I need to do substring and I want to do substring of this whole thing.

81
00:05:14,470 --> 00:05:18,430
And you know, I'm realizing as this goes on, this is a little convoluted.

82
00:05:18,430 --> 00:05:21,200
So I'll throw up another don't panic here.

83
00:05:21,220 --> 00:05:24,550
This is more just me showing you that it's something you can do.

84
00:05:24,730 --> 00:05:29,200
But it's not essential that you wrap your head around this entirely.

85
00:05:29,500 --> 00:05:31,780
But just to show you so substring.

86
00:05:31,780 --> 00:05:35,620
And the first argument is replace this whole thing.

87
00:05:35,620 --> 00:05:42,310
So basically we're taking a substring of this and then if this and we want to go from 1 to 10.

88
00:05:44,350 --> 00:05:45,280
So that's a lot there.

89
00:05:45,280 --> 00:05:53,410
And now if I paste it in here, you can see what it did was first replace all ease with three and then

90
00:05:53,410 --> 00:05:56,620
take the substring of that from 1 to 10.

91
00:05:58,720 --> 00:06:02,020
And of course, we could give it a better name because it is pretty ugly.

92
00:06:02,020 --> 00:06:06,370
So we could do as weird string, let's say.

93
00:06:08,130 --> 00:06:11,850
And now we have weird string up here and yeah, that's pretty much it.

94
00:06:11,850 --> 00:06:14,220
I just wanted to show that we could combine them together.

95
00:06:14,310 --> 00:06:15,720
Why you would do this?

96
00:06:15,720 --> 00:06:16,260
Exactly.

97
00:06:16,260 --> 00:06:17,790
Again, you probably wouldn't.

98
00:06:18,090 --> 00:06:20,700
But the key here is that they're kind of like Lego pieces.

99
00:06:20,700 --> 00:06:22,560
You can reorder them.

100
00:06:22,560 --> 00:06:23,490
You can.

101
00:06:23,850 --> 00:06:29,670
Well, maybe more like nesting dolls, actually, where you can nest them inside of one another.

102
00:06:29,670 --> 00:06:34,770
So like we did substring inside of a cat, we can do a replace inside of substring.

103
00:06:34,770 --> 00:06:36,510
So that's enough of replace.

104
00:06:36,510 --> 00:06:39,540
We're going to continue with a couple more and then we'll do some challenges.