1 00:00:00,090 --> 00:00:01,050 So that's the basics. 2 00:00:01,050 --> 00:00:05,160 But there's another wild card character that I'll show you, which is the underscore. 3 00:00:05,880 --> 00:00:08,880 So here I have a really weird query. 4 00:00:09,120 --> 00:00:11,310 This is for underscores in a row. 5 00:00:12,240 --> 00:00:13,740 You just have to trust me that it's for. 6 00:00:13,740 --> 00:00:17,040 And it was hard to tell what this will do. 7 00:00:17,220 --> 00:00:24,180 So we're searching stock quantity, which is a number, and we're trying to find where it's like for 8 00:00:24,180 --> 00:00:24,950 underscores. 9 00:00:24,960 --> 00:00:31,410 And basically an underscore is a wild card to specify exactly one character, any character, but exactly 10 00:00:31,410 --> 00:00:31,770 one. 11 00:00:31,770 --> 00:00:35,880 So four of them in a row is saying, I want to find where the stock quantity is. 12 00:00:35,880 --> 00:00:37,320 Four characters long. 13 00:00:37,830 --> 00:00:40,930 So let me show you why we might want to do that. 14 00:00:40,950 --> 00:00:48,630 If I do a select stock, let's do title and stock quantity from books. 15 00:00:48,840 --> 00:00:49,800 Just like that. 16 00:00:50,450 --> 00:00:54,890 You can see that we have stock quantity of two or sorry, two digits. 17 00:00:54,920 --> 00:00:57,130 Two digits, 30 to 43. 18 00:00:57,140 --> 00:00:58,100 Three digits. 19 00:00:58,130 --> 00:01:00,110 Geez, it's given me a tough time. 20 00:01:00,140 --> 00:01:09,350 Digits, 154 And then this great book linking in the Bardo has 1000 copies in stock is brand new. 21 00:01:09,380 --> 00:01:10,670 People don't know about it yet. 22 00:01:11,030 --> 00:01:11,720 They're not buying it. 23 00:01:11,720 --> 00:01:13,100 I'm sure people will buy it soon, though. 24 00:01:13,100 --> 00:01:15,830 Once, of course, they realize what a great book it is. 25 00:01:16,280 --> 00:01:22,910 So if I wanted to select books where stock quantity is four digits long, I could use that wild card. 26 00:01:23,030 --> 00:01:24,500 So let me just show that again. 27 00:01:24,500 --> 00:01:34,350 So select title and stock quantity from books where stock quantity like and all I have to do. 28 00:01:34,370 --> 00:01:39,410 So just to clarify, if I did this, that wild card just means anything. 29 00:01:39,920 --> 00:01:41,540 So that's going to give us everything. 30 00:01:41,930 --> 00:01:44,300 And if I do four of them, it doesn't make a difference. 31 00:01:44,450 --> 00:01:46,130 That's not saying four characters. 32 00:01:46,130 --> 00:01:48,950 That's just four different ways of saying anything at all. 33 00:01:48,980 --> 00:01:50,090 It could be nothing. 34 00:01:50,090 --> 00:01:51,470 It could be 1000 characters. 35 00:01:51,470 --> 00:01:52,220 It doesn't matter. 36 00:01:53,420 --> 00:01:58,310 So by adding underscores, that's for there. 37 00:01:59,030 --> 00:02:06,710 You'll see I get Lincoln in the Bardo or I could do two underscores and I only get the two digit stock 38 00:02:06,710 --> 00:02:07,370 quantities. 39 00:02:07,850 --> 00:02:11,290 Now, that's not to say the only time you use underscores is when you're trying. 40 00:02:11,300 --> 00:02:13,400 Yes, Blue, it's my cat. 41 00:02:14,150 --> 00:02:15,230 Are you going to say hi? 42 00:02:15,440 --> 00:02:16,970 She's now sitting on the computer. 43 00:02:18,370 --> 00:02:19,880 Okay, so back to where I was. 44 00:02:20,750 --> 00:02:22,250 Get this cat hair out of here. 45 00:02:23,480 --> 00:02:30,110 The underscore is not only used and actually it's not even primarily used for counting digits like that, 46 00:02:30,110 --> 00:02:33,500 but it's a way of specifying exactly one character. 47 00:02:33,500 --> 00:02:38,480 Like I said, let's say that we're working with phone numbers and in the US they might look something 48 00:02:38,480 --> 00:02:39,410 like this. 49 00:02:39,500 --> 00:02:44,960 So we have an area code in parentheses and then three digits dash for digits. 50 00:02:45,290 --> 00:02:51,830 And if we wanted to match that pattern, we would have something that looks like this like and we'd 51 00:02:51,830 --> 00:02:53,060 have our first parentheses. 52 00:02:53,630 --> 00:02:58,910 And if we just put this wild card there that's going to say any number, any letters, whatever it is 53 00:02:59,030 --> 00:03:00,260 in between those parentheses. 54 00:03:00,260 --> 00:03:06,710 But we're actually looking for three digits and then another three digits after that parentheses, then 55 00:03:06,710 --> 00:03:08,990 a dash and then four digits. 56 00:03:09,860 --> 00:03:15,500 So basically we have these two wild cards at the end of the day, present sign, which means anything 57 00:03:15,500 --> 00:03:23,000 or nothing and then underscore which is exactly one thing and you can chain them together to match patterns. 58 00:03:23,660 --> 00:03:29,390 Then that brings up another question, which is, but what if I'm searching for a book that has a percent 59 00:03:29,390 --> 00:03:30,230 sign in it? 60 00:03:30,410 --> 00:03:31,700 How would I do that? 61 00:03:32,030 --> 00:03:37,790 And you may have noticed that I expertly planted a book title with a percent sign in it. 62 00:03:38,540 --> 00:03:42,500 I was thinking ahead for once, how would we select that? 63 00:03:43,340 --> 00:03:47,630 Or there's also a book title that's not so creative that has an underscore in it. 64 00:03:47,630 --> 00:03:49,070 How would I select that one? 65 00:03:49,190 --> 00:03:57,020 If these characters are special wild card characters, how do I actually search for the exact character 66 00:03:57,020 --> 00:03:59,900 percent sign or the exact character underscore? 67 00:03:59,900 --> 00:04:01,910 And the answer is actually pretty straightforward. 68 00:04:03,740 --> 00:04:06,740 We use what's known as an escape character. 69 00:04:07,430 --> 00:04:13,460 In this case, I always forget which one's backwards and forwards slash but whichever slash, this is 70 00:04:14,030 --> 00:04:15,680 the one over the enter key. 71 00:04:15,890 --> 00:04:17,930 Pretty sure that is backslash. 72 00:04:18,649 --> 00:04:23,140 So backslash percent sign is going to match a percent sign. 73 00:04:23,150 --> 00:04:27,470 So this entire thing right here is basically saying percent sign. 74 00:04:28,460 --> 00:04:30,380 Likewise with underscore, it's backslash. 75 00:04:30,380 --> 00:04:32,390 Underscore is the escape character. 76 00:04:32,660 --> 00:04:35,540 So let's put that into practice, let's try it out. 77 00:04:35,540 --> 00:04:42,320 So we'll select title from books and we'll just do that first of all. 78 00:04:42,650 --> 00:04:46,130 And you'll see we've got 10% happier and then fake underscore book. 79 00:04:46,130 --> 00:04:47,720 So let's start with 10% happier. 80 00:04:47,960 --> 00:04:56,240 So I want to select title from books from books where title like and just I mean, this should be clear 81 00:04:56,240 --> 00:05:00,020 by now, but if we try to do this, that's not going to work. 82 00:05:00,020 --> 00:05:01,550 That's going to give us everything. 83 00:05:01,550 --> 00:05:07,460 But if I tried to say, okay, percent sign, that means anything, and then a percent sign and then 84 00:05:07,460 --> 00:05:11,480 anything afterwards, that's not going to work either because that's basically just three different 85 00:05:11,480 --> 00:05:13,550 ways of saying anything at all in the title. 86 00:05:13,850 --> 00:05:15,710 So that's where we need our backslash. 87 00:05:16,940 --> 00:05:23,240 And now you could read this as you could condense this to say exactly 8% sign, one percentage sign, 88 00:05:23,510 --> 00:05:25,640 anything before, anything after. 89 00:05:26,240 --> 00:05:27,200 And there we go. 90 00:05:27,200 --> 00:05:29,120 That's how we get 10% happier. 91 00:05:30,170 --> 00:05:31,580 Same thing with the underscore. 92 00:05:31,580 --> 00:05:33,560 I won't go through that whole routine again. 93 00:05:34,530 --> 00:05:41,760 We just replaced that backslash percent with backslash underscore and that gives us fake book. 94 00:05:41,880 --> 00:05:42,570 All right. 95 00:05:42,570 --> 00:05:45,420 So that's all that there is to like for now. 96 00:05:45,750 --> 00:05:47,090 It's very, very useful. 97 00:05:47,100 --> 00:05:48,960 We'll be using it quite a bit in this course. 98 00:05:49,110 --> 00:05:54,090 It's probably one of the most commonly used things in my SQL or in SQL when doing queries. 99 00:05:54,120 --> 00:05:59,790 In all likelihood, you won't be matching percent signs or underscores all that often, but you'll be 100 00:05:59,790 --> 00:06:06,420 doing things like this all the time where you're using the wild card and the underscore wild card to 101 00:06:06,420 --> 00:06:07,350 match patterns. 102 00:06:08,280 --> 00:06:09,720 Next up, exercises.