1 00:00:00,150 --> 00:00:00,540 All right. 2 00:00:00,540 --> 00:00:01,810 So we have that out of the way. 3 00:00:01,830 --> 00:00:04,920 We've seen how to run files using the source command. 4 00:00:04,920 --> 00:00:07,920 And then we actually did that with the sample data that I gave you. 5 00:00:07,950 --> 00:00:12,780 Now we actually dive into the meat of the section, which is all about string functions. 6 00:00:12,990 --> 00:00:21,120 And the first thing I want to show you is that if you go to Google and we type in my SQL string functions, 7 00:00:22,290 --> 00:00:27,510 oops, string functions, excuse that and we hit enter here. 8 00:00:28,170 --> 00:00:33,450 You can see I've already clicked on this link the my SQL reference manual string functions. 9 00:00:34,350 --> 00:00:39,240 I have to say I'm not a huge fan of the documentation of my SQL. 10 00:00:39,240 --> 00:00:45,450 For one thing, I think it's kind of ugly and looks dated, but it does have all of the information 11 00:00:45,450 --> 00:00:46,220 you need. 12 00:00:46,230 --> 00:00:48,150 I just don't really like how it's presented. 13 00:00:48,150 --> 00:00:52,710 But if you if you look at this giant list here, these are all string functions that we can use. 14 00:00:52,830 --> 00:00:55,550 And a lot of them are very, very specific. 15 00:00:55,560 --> 00:00:57,420 I'm going to show you some of the more. 16 00:00:57,810 --> 00:01:02,670 Well, first of all, the useful ones and then a couple just fun ones and that we'll be able to combine 17 00:01:02,670 --> 00:01:04,319 together for some good exercises. 18 00:01:04,319 --> 00:01:06,690 But we're not going to take a look at most of these. 19 00:01:06,690 --> 00:01:10,320 Most people don't even know that these are here. 20 00:01:10,650 --> 00:01:12,870 I'd say that's a generalization. 21 00:01:12,870 --> 00:01:20,100 But in my experience, working with my SQL and people who work with my SQL, you use maybe five of these 22 00:01:20,850 --> 00:01:25,890 on a regular basis, and then occasionally you might need to do something like, Hey, is there a way 23 00:01:25,890 --> 00:01:26,940 of doing X? 24 00:01:26,940 --> 00:01:32,010 Let me go check on the documentation and oh, look, there is a way of doing that perfect. 25 00:01:32,820 --> 00:01:34,530 Or Oh no, there's not a way. 26 00:01:34,560 --> 00:01:36,570 Great, what am I going to do next? 27 00:01:36,930 --> 00:01:38,880 So I just want to show you that this is here. 28 00:01:39,090 --> 00:01:42,660 And the first one that we're actually going to take a look at is called Cat. 29 00:01:43,050 --> 00:01:47,160 So what it will do is combine pieces of data to combine strings. 30 00:01:47,160 --> 00:01:53,040 So here's an example using this book data, remember that we have author first name and author last 31 00:01:53,040 --> 00:01:53,520 name. 32 00:01:54,200 --> 00:01:56,930 And there are valid reasons for separating that data. 33 00:01:56,960 --> 00:02:03,920 Sometimes, I mean, you might want to sort books by last name and sometimes you may only want the last 34 00:02:03,920 --> 00:02:05,630 name in certain contexts. 35 00:02:05,630 --> 00:02:07,010 You might not want the first name. 36 00:02:07,460 --> 00:02:11,160 It's not a great example, but there are reasons that you would separate this out. 37 00:02:11,180 --> 00:02:15,490 Just like when you're filling out a form online, you're signing up for a site. 38 00:02:15,500 --> 00:02:17,750 They usually don't just ask you for your full name. 39 00:02:17,750 --> 00:02:19,670 They ask you for your first and last name. 40 00:02:20,000 --> 00:02:24,180 But then if they have those pieces of information, it's pretty easy to combine them. 41 00:02:24,200 --> 00:02:25,670 So that's what we're going to see now. 42 00:02:25,670 --> 00:02:33,140 How do we combine these two and get something that says Jhumpa Lahiri or Neil Gaiman or in this case, 43 00:02:33,140 --> 00:02:38,000 Foster Wallace as the last name, which I'm actually not sure if that qualifies as a last name. 44 00:02:38,000 --> 00:02:38,660 Exactly. 45 00:02:38,660 --> 00:02:41,320 But David Foster Wallace, how did we combine that? 46 00:02:41,330 --> 00:02:42,290 How do we get that? 47 00:02:42,290 --> 00:02:44,150 And the answer is we use Concatenate. 48 00:02:44,420 --> 00:02:46,790 So let me just first show you what we have right now. 49 00:02:47,390 --> 00:02:55,880 We'll do a select author f name, author l name from books. 50 00:02:57,230 --> 00:02:58,580 And that's what we get here. 51 00:02:58,670 --> 00:03:01,190 Just what I had printed out in the slides. 52 00:03:01,520 --> 00:03:03,620 So what we want again is to combine them. 53 00:03:03,620 --> 00:03:08,660 But we'll take some baby steps and yep, here's a slide I totally got ahead of myself. 54 00:03:08,660 --> 00:03:10,130 What if I want full names? 55 00:03:11,450 --> 00:03:12,710 So can Cat. 56 00:03:12,740 --> 00:03:18,350 The way that Kin Cat works is that we pass in multiple arguments or pieces of data and it will combine 57 00:03:18,350 --> 00:03:18,650 them. 58 00:03:18,650 --> 00:03:23,300 So whatever we give it in this case, I mean, if you had columns named X, Y and Z, this would work. 59 00:03:23,300 --> 00:03:25,250 But it's really just supposed to be an example. 60 00:03:25,820 --> 00:03:32,420 More specifically, what you can do are things like this can cat a column name with another column. 61 00:03:32,420 --> 00:03:33,710 So that's what we'll work on. 62 00:03:33,710 --> 00:03:39,320 We're going to do first name or author f name and author l name and combine them together. 63 00:03:39,320 --> 00:03:44,000 And if we do this right here, so can cat author f name, author l name. 64 00:03:44,510 --> 00:03:50,270 What it will actually do is just slam them together, put them exactly together, concatenate them so 65 00:03:50,270 --> 00:03:51,590 there's no space in between. 66 00:03:51,590 --> 00:03:53,060 So we get Dave Eggers. 67 00:03:53,240 --> 00:03:54,260 Jhumpa Lahiri. 68 00:03:54,560 --> 00:03:57,260 So there's no that's not exactly what we want. 69 00:03:57,290 --> 00:04:02,300 Maybe we want a comma, maybe we want last name, comma, first name, or maybe we want first name, 70 00:04:02,300 --> 00:04:03,680 space, last name. 71 00:04:03,680 --> 00:04:05,300 So there's a way of doing that. 72 00:04:05,300 --> 00:04:11,840 When we can cat, we can pass in a column name and then some other text and then another column name. 73 00:04:11,840 --> 00:04:16,459 And if we wanted more text, we can keep going on and on and on and it will concatenate everything. 74 00:04:16,910 --> 00:04:26,530 So to add a space, it's pretty simple author, first name, comma, and then a space comma author l 75 00:04:26,540 --> 00:04:26,990 name. 76 00:04:27,110 --> 00:04:30,650 And that will give us Dave Eggers and Jhumpa Lahiri. 77 00:04:30,980 --> 00:04:34,070 So now that I've shown you in the slides, let's take a look. 78 00:04:34,460 --> 00:04:39,590 And the first thing I'll show is that often people expect that they can just come in here and and just 79 00:04:39,590 --> 00:04:40,820 type canned cat. 80 00:04:41,180 --> 00:04:50,450 But if I just do canned cat, well, author f name, comma, author l name. 81 00:04:51,590 --> 00:04:52,520 There's a problem. 82 00:04:52,550 --> 00:04:54,680 Hopefully you can identify what the problem is. 83 00:04:54,680 --> 00:04:57,500 If I hit enter, this is not valid syntax. 84 00:04:57,500 --> 00:05:00,230 First of all, it doesn't know what table. 85 00:05:00,650 --> 00:05:02,630 What if we have multiple tables? 86 00:05:02,990 --> 00:05:05,480 Where's author if name coming from an author l name. 87 00:05:05,720 --> 00:05:07,580 And second of all, we have to select. 88 00:05:07,580 --> 00:05:09,950 So we can't just run these willy nilly. 89 00:05:10,220 --> 00:05:11,810 We can't just run them on their own. 90 00:05:11,810 --> 00:05:14,450 We need to run them as a select. 91 00:05:14,720 --> 00:05:17,270 So select can cat. 92 00:05:17,630 --> 00:05:20,330 And then in here, I'm actually going to start with something simple. 93 00:05:20,330 --> 00:05:22,760 I'm not going to use columns, I'm going to use text. 94 00:05:22,970 --> 00:05:30,350 So I'll just put something like Hello Comma World. 95 00:05:30,560 --> 00:05:37,730 So this is very trivial, but if I just do this select can cat hello world and I hit enter you can see 96 00:05:37,730 --> 00:05:43,940 it doesn't for me and it just gives me hello world combines these two things so I could do the same 97 00:05:43,940 --> 00:05:51,920 thing and add a space and rather than a space, let's do dots so it's easier to see and we need a comma 98 00:05:52,340 --> 00:05:53,780 a lot of punctuation going on. 99 00:05:54,050 --> 00:05:58,970 Select can cat hello comma dot dot comma world. 100 00:05:58,970 --> 00:06:03,410 It just shoves them all together and we get hello dot dot dot world. 101 00:06:04,070 --> 00:06:08,240 So that's how we can do it with just text, which you probably won't be doing. 102 00:06:08,690 --> 00:06:17,060 You'll be doing things like this select and then can cat a column like author f name and then a space 103 00:06:17,060 --> 00:06:20,090 and then author l name from books. 104 00:06:21,020 --> 00:06:22,790 So let's try it. 105 00:06:22,820 --> 00:06:23,630 We can copy it. 106 00:06:23,630 --> 00:06:24,140 Now. 107 00:06:25,250 --> 00:06:26,660 Go over here and paste it. 108 00:06:27,500 --> 00:06:28,310 Select. 109 00:06:28,430 --> 00:06:34,220 And what it will do when we hit enter is just make a new column for us or in our results. 110 00:06:34,220 --> 00:06:35,870 It doesn't change our initial data. 111 00:06:35,870 --> 00:06:39,160 Just to be clear, our data looks the same in the database. 112 00:06:39,170 --> 00:06:41,180 This is purely about printing it out. 113 00:06:41,750 --> 00:06:46,670 But we have this new thing up here with this horrible title can cat author f name comma space comma 114 00:06:46,700 --> 00:06:47,510 author l name. 115 00:06:47,990 --> 00:06:50,480 But then in here we have the data that we're looking for. 116 00:06:50,780 --> 00:06:55,280 We have Jhumpa Lahiri, we have Patti Smith, Raymond Carver and so on. 117 00:06:55,970 --> 00:06:58,910 So that's really useful to combine data like that. 118 00:06:59,240 --> 00:07:02,930 Now, if we want to get a little fancier, let's rename it. 119 00:07:03,710 --> 00:07:09,050 So let's give it an as and we'll just say as full name just like that. 120 00:07:09,530 --> 00:07:12,170 And now I get full name up here. 121 00:07:12,410 --> 00:07:14,300 Jhumpa Lahiri, Neil Gaiman and so on. 122 00:07:14,780 --> 00:07:16,040 So that can Cat. 123 00:07:16,040 --> 00:07:21,290 I'll show you a couple more examples just to show you how these string functions work. 124 00:07:21,290 --> 00:07:23,090 You don't have to just do one at a time. 125 00:07:23,240 --> 00:07:30,830 We could do something like this select and we'll do author first name just on its own. 126 00:07:31,250 --> 00:07:35,120 Author F name as first name. 127 00:07:37,260 --> 00:07:51,270 Let's just do first actually as first comma, author l name as last and then we'll have a cat author 128 00:07:51,270 --> 00:07:58,830 and let's do it on a new line just because it gets a little messy here can cat author f name. 129 00:07:58,830 --> 00:08:03,840 So this is what we've already seen with a space comma author l name. 130 00:08:06,920 --> 00:08:09,020 As and we'll call it full. 131 00:08:09,020 --> 00:08:11,330 So we'll have three different things printed out. 132 00:08:11,360 --> 00:08:17,900 The first name under the title, first author LL name as last, and then the full name combined using 133 00:08:18,050 --> 00:08:19,640 cat as full. 134 00:08:20,600 --> 00:08:23,090 So then we need to say from where? 135 00:08:23,210 --> 00:08:24,920 From books. 136 00:08:25,610 --> 00:08:30,350 Now I hit enter and I get this pretty little table first last in full. 137 00:08:31,010 --> 00:08:34,100 So what I want to show is that you don't have to use it in isolation. 138 00:08:34,100 --> 00:08:37,700 You can combine it with other things and you can and we'll see this as we go. 139 00:08:37,700 --> 00:08:43,909 We're not going to just use Cat and you don't have to just use one can cat per select. 140 00:08:43,909 --> 00:08:48,500 You could have multiple if we wanted to, you know, I could have full name and then I could have full 141 00:08:48,500 --> 00:08:50,930 names comma separated instead. 142 00:08:51,050 --> 00:08:52,460 And I want to do that. 143 00:08:52,970 --> 00:08:59,090 You can probably figure this out, but rather than just having a space there, I'll just put a comma. 144 00:09:00,820 --> 00:09:03,910 And now I get Jhumpa Lahiri. 145 00:09:04,390 --> 00:09:05,020 All right. 146 00:09:05,230 --> 00:09:11,530 So one more thing about Venkat is that there is something called Venkat WC, which stands for Canned 147 00:09:11,530 --> 00:09:12,930 Cat with Separator. 148 00:09:12,940 --> 00:09:18,700 So there might be times where you're concatenating, concatenating, multiple fields, multiple things 149 00:09:18,700 --> 00:09:23,830 together with the same symbol or piece of data stuck in between them. 150 00:09:24,520 --> 00:09:26,050 So there's a shortcut for that. 151 00:09:26,770 --> 00:09:33,520 So if I wanted to have Title Dash, author F named Dash, author last name, let's not debate why. 152 00:09:33,550 --> 00:09:36,550 But if I wanted to, I could do something like this. 153 00:09:36,670 --> 00:09:48,280 Select Venkat and we have author or actually we started with title comma and then we wanted a dash and 154 00:09:48,280 --> 00:09:56,170 then author F name comma and then another dash comma, author l name just like that. 155 00:09:56,650 --> 00:10:02,260 So it's kind of a lot of repetitive typing, especially if we have more items that we're combining. 156 00:10:03,010 --> 00:10:05,470 But if I just do this here from books. 157 00:10:06,770 --> 00:10:08,490 You see, it does what you expect. 158 00:10:08,510 --> 00:10:11,360 It puts the title dash first, dash last. 159 00:10:12,020 --> 00:10:15,560 But we can use contact us and it looks like this. 160 00:10:16,520 --> 00:10:18,470 So it switches things around a bit. 161 00:10:18,500 --> 00:10:22,220 The first thing that we pass in is what we want the separator to be. 162 00:10:22,220 --> 00:10:28,100 So in this case, a dash with the space on either side and it will go ahead and then insert that in 163 00:10:28,100 --> 00:10:30,170 between every other thing we pass in. 164 00:10:30,170 --> 00:10:31,280 So we'll have title. 165 00:10:31,310 --> 00:10:32,870 Dash Author First Name. 166 00:10:32,870 --> 00:10:34,190 Dash Author last name. 167 00:10:35,960 --> 00:10:39,800 So now if I paste that in, you can see we get the same result. 168 00:10:39,800 --> 00:10:45,200 Although I added spaces here but at the same idea it send which every time. 169 00:10:45,980 --> 00:10:51,620 So just worth noting, if you ever run a situation where you're combining lots of data in the same manner, 170 00:10:51,650 --> 00:10:57,110 you can use us to save you typing this or whatever your symbol is over and over. 171 00:10:57,350 --> 00:11:01,500 So that's our first string function we saw can cut and contact us. 172 00:11:01,730 --> 00:11:07,370 And what they do is concatenate or shove together, smash together strings.