1
00:00:00,120 --> 00:00:01,620
Next up, we're going to cover the like.

2
00:00:01,650 --> 00:00:03,180
Operator in MySQL.

3
00:00:03,210 --> 00:00:06,960
It's a pretty useful operator to help us perform basic searching.

4
00:00:07,260 --> 00:00:12,230
So at the moment, we obviously can do things like find it an exact match.

5
00:00:12,240 --> 00:00:17,350
If I'm trying to find a book written by somebody with David as their first name.

6
00:00:17,370 --> 00:00:20,970
I can do select and then let's do author.

7
00:00:21,000 --> 00:00:22,110
Let's do title.

8
00:00:23,250 --> 00:00:29,600
And then author F name, author ll name from books.

9
00:00:29,610 --> 00:00:36,750
And then we can do something like where author first name exactly equals David.

10
00:00:36,750 --> 00:00:37,830
And that's great.

11
00:00:37,830 --> 00:00:38,730
That works.

12
00:00:38,730 --> 00:00:44,510
And we get the two books written by David, but that has to be an exact match, right?

13
00:00:44,520 --> 00:00:49,020
It doesn't work if it's Dave or Dave or something like that.

14
00:00:49,530 --> 00:00:55,560
But a lot of the time we may want to perform some fuzzier searching where it's not based only on an

15
00:00:55,560 --> 00:00:58,110
exact match, and that's where like comes in.

16
00:00:58,110 --> 00:01:00,360
Imagine we're looking for a book.

17
00:01:00,360 --> 00:01:02,250
We can't remember the exact title.

18
00:01:02,250 --> 00:01:03,540
We'll know it when we see it.

19
00:01:03,540 --> 00:01:10,170
But I know the author's first name is David or Dan or Dave or something with a D and then an A.

20
00:01:10,440 --> 00:01:16,890
Well, what we can do is use the like operator followed by a piece of text, a string in quotes that

21
00:01:16,890 --> 00:01:19,950
has a little bit of special magic to it.

22
00:01:19,950 --> 00:01:23,760
Those special characters, those percent signs mean something.

23
00:01:23,760 --> 00:01:25,470
They're called wild cards.

24
00:01:25,470 --> 00:01:32,550
And in this situation, this percent sign and this percent sign mean any number of characters, zero

25
00:01:32,550 --> 00:01:35,820
or more characters, zero or more characters.

26
00:01:35,820 --> 00:01:41,490
So in other words, we're saying, I want to find something where the author's first name follows this

27
00:01:41,490 --> 00:01:42,150
pattern.

28
00:01:42,180 --> 00:01:47,280
There is zero or more characters and then a D in an A, and then zero or more characters.

29
00:01:47,280 --> 00:01:49,380
So let me show this as an example.

30
00:01:50,070 --> 00:01:51,960
Let's do the same thing.

31
00:01:51,960 --> 00:02:00,600
We'll select title first name, last name from books where and then we'll say author first name like,

32
00:02:01,020 --> 00:02:02,910
and then I'll do this on the next line.

33
00:02:02,910 --> 00:02:10,380
I guess just because I want this to be easy to read where author first name is like this pattern I'm

34
00:02:10,380 --> 00:02:11,190
going to lay out.

35
00:02:11,190 --> 00:02:14,250
And if I simply say D A, it's not going to work.

36
00:02:14,580 --> 00:02:22,050
I don't have any matches that are exactly DA But if I instead put a percent sign first and then D A

37
00:02:22,410 --> 00:02:27,090
and then D rather then percent sign again, Take a look.

38
00:02:27,120 --> 00:02:29,190
I found a match here.

39
00:02:29,220 --> 00:02:32,310
Da da da da da da.

40
00:02:32,340 --> 00:02:34,560
And one more day.

41
00:02:35,340 --> 00:02:41,430
So again, the percent sign means optionally zero more zero or more characters.

42
00:02:41,430 --> 00:02:45,810
So it just is saying if we have this pattern percent da.

43
00:02:46,690 --> 00:02:50,220
Percent that it could be at the beginning of the string.

44
00:02:50,230 --> 00:02:55,420
There's nothing here in the case of Dave and David and Dan, or it could be in the middle, which we

45
00:02:55,420 --> 00:03:01,210
don't have right now, or it could even be at the end of the string because this is optional as well.

46
00:03:01,840 --> 00:03:04,000
So let's take a look at another example.

47
00:03:04,030 --> 00:03:09,970
Let's say I want to find any of these collections or any of these books that have a colon in their title.

48
00:03:10,330 --> 00:03:17,710
So I'll just do a select star from books and then I can do where title like.

49
00:03:18,100 --> 00:03:24,250
And then after that, like I can not just write a colon because of course that will only match the exact

50
00:03:24,250 --> 00:03:25,240
match of a colon.

51
00:03:25,240 --> 00:03:30,860
But I'll say some text maybe, and then a colon and then some text possibly afterwards.

52
00:03:30,880 --> 00:03:35,140
In other words, this is saying I just want to see if there's a colon anywhere in the string, the beginning,

53
00:03:35,140 --> 00:03:36,220
the middle or the end.

54
00:03:36,220 --> 00:03:37,810
And it's ugly to look at.

55
00:03:37,810 --> 00:03:40,210
But here we are, hologram for the king Colon.

56
00:03:40,210 --> 00:03:44,620
What we talk about when we talk about Love Colon, where I'm calling from, colon Oblivion.

57
00:03:44,620 --> 00:03:45,370
Colon.

58
00:03:45,370 --> 00:03:49,840
So that is the first of the wild, wild card characters.

59
00:03:49,840 --> 00:03:51,040
You'll see the percent sign.

60
00:03:51,040 --> 00:03:54,430
There's one more I'm going to show you, which is the underscore.

61
00:03:54,430 --> 00:03:57,940
And an underscore means something different than the present sign.

62
00:03:57,940 --> 00:04:01,120
It means exactly one character.

63
00:04:01,510 --> 00:04:05,620
So I could do something like I want to find.

64
00:04:05,620 --> 00:04:07,270
It's hard to come up with a good example here.

65
00:04:07,270 --> 00:04:08,560
Let's look at our books again.

66
00:04:10,370 --> 00:04:19,160
I want to select the books that let's say I want to find all authors who have a first name that is exactly,

67
00:04:20,149 --> 00:04:22,730
I don't know, exactly three or four characters.

68
00:04:22,730 --> 00:04:23,360
Why?

69
00:04:23,360 --> 00:04:23,870
I don't know.

70
00:04:23,870 --> 00:04:25,190
But let's say I want to do that.

71
00:04:25,190 --> 00:04:33,830
I'll do a select start from books where and then author f name like, and then my string is going to

72
00:04:33,830 --> 00:04:36,200
be one character, two, three, four.

73
00:04:36,200 --> 00:04:38,000
It's exactly four underscores.

74
00:04:38,000 --> 00:04:40,640
Remember, those underscores don't mean underscore.

75
00:04:40,670 --> 00:04:42,740
They mean one character.

76
00:04:42,740 --> 00:04:44,480
It must be one character.

77
00:04:45,310 --> 00:04:51,880
And we find Neil, Neil, Dave, Dave, Dave, Neil and John, all of those four character authors.

78
00:04:51,910 --> 00:04:58,090
So if I had instead added a fifth underscore, now we get all the books written by authors that have

79
00:04:58,090 --> 00:04:59,560
five characters in their name.

80
00:04:59,590 --> 00:05:01,660
Patty, David, David.

81
00:05:02,500 --> 00:05:06,190
If I instead did percent signs, remember what this means.

82
00:05:06,190 --> 00:05:09,550
That percent sign means zero or more characters.

83
00:05:09,550 --> 00:05:11,370
So this could match zero characters.

84
00:05:11,380 --> 00:05:16,150
It could match 1000 characters, although our var strings don't support something that long.

85
00:05:16,480 --> 00:05:17,560
The way I have it set up.

86
00:05:17,650 --> 00:05:21,010
So we get every single author, every row in our data set.

87
00:05:21,010 --> 00:05:24,930
As long as there is an author f name, I think one of them has null.

88
00:05:24,940 --> 00:05:26,200
So we're not getting that one.

89
00:05:27,280 --> 00:05:32,020
So that's really important to understand the difference percent sign you use generally if you want to

90
00:05:32,020 --> 00:05:39,600
find some match within a string, anywhere within a string, you're looking for some smaller portion.

91
00:05:39,610 --> 00:05:44,680
You use the underscores if you know with precise detail that you're looking for something of a certain

92
00:05:44,680 --> 00:05:51,040
number of characters, or if I don't know, this might be really bizarre, but let's say I want to find

93
00:05:51,040 --> 00:05:52,060
this pattern.

94
00:05:52,690 --> 00:05:58,720
I want to find authors who have a first name where it is a letter and a character, and then exactly

95
00:05:58,720 --> 00:06:01,900
one more character, and we find Dan.

96
00:06:02,080 --> 00:06:08,620
But if I had instead just had a percent sign on either side, this says, Find any authors first name

97
00:06:08,620 --> 00:06:10,750
that has an A anywhere in it.

98
00:06:10,750 --> 00:06:12,760
And a lot of them do, right?

99
00:06:12,760 --> 00:06:14,560
Jhumpa the A is at the end.

100
00:06:14,770 --> 00:06:16,200
Dave It's in the middle.

101
00:06:16,210 --> 00:06:18,650
Michael It's kind of in the middle towards the end.

102
00:06:18,670 --> 00:06:26,380
Another thing we could do if I wanted to find all of the author's first names that let's say, end with

103
00:06:26,380 --> 00:06:29,290
a an N, they end of the letter.

104
00:06:29,290 --> 00:06:38,500
N to say that it must end with that letter, I could do select star from books where author I think

105
00:06:38,500 --> 00:06:46,120
I said first name and then like and my pattern is going to say anything and then mandatory n at the

106
00:06:46,120 --> 00:06:48,370
end with nothing afterwards.

107
00:06:48,940 --> 00:06:53,860
And we get Don, John and Dan and that's pretty much it for this video.

108
00:06:53,860 --> 00:06:55,960
Like is powerful.

109
00:06:55,960 --> 00:06:57,700
It's not fully powerful.

110
00:06:57,700 --> 00:07:02,800
Like if we if you know, regular expressions or something, but it still offers a lot of nice searching

111
00:07:02,800 --> 00:07:05,080
features thanks to those wild card characters.

112
00:07:05,080 --> 00:07:09,760
Remember, a percent sign means anything zero or more characters.

113
00:07:09,970 --> 00:07:14,110
And then a single underscore means exactly one character.