1 00:00:00,300 --> 00:00:04,890 So we're going to talk about the first of a handful of string functions in this video. 2 00:00:04,920 --> 00:00:11,280 The string function we're starting with is called concatenate, which is short for concatenate or combining 3 00:00:11,280 --> 00:00:13,410 strings or combining text together. 4 00:00:13,710 --> 00:00:17,560 Now, recall that there's a bunch of these functions they all have to do with text. 5 00:00:17,580 --> 00:00:22,080 If we go to the docs and you click on contact, you'll see an example of it. 6 00:00:22,080 --> 00:00:26,280 But what you won't see is an example using any data and a tables. 7 00:00:26,280 --> 00:00:32,610 Instead you'll see something like this select can cat and then just some text that's put in there. 8 00:00:32,700 --> 00:00:38,970 Now this is kind of odd, but it's totally valid in the same way that I showed you way early on. 9 00:00:38,970 --> 00:00:44,080 We can do something like this, select one plus four and use SQL as a calculator. 10 00:00:44,100 --> 00:00:47,310 It's clunky, it's weird, but we get five as a result. 11 00:00:47,490 --> 00:00:52,950 So most of what we're going to be doing with Can Cat is working with a table and concatenating different 12 00:00:52,950 --> 00:00:55,820 pieces of text together from a table from different rows. 13 00:00:55,830 --> 00:01:02,400 But we could start by doing something simple and concatenating two pieces of text or random text together 14 00:01:02,400 --> 00:01:06,180 that has nothing to do with a real table full of data. 15 00:01:06,270 --> 00:01:07,950 So it can cat will take. 16 00:01:08,670 --> 00:01:15,810 If I just type it out here inside of parentheses any number of strings or pieces of text, like let's 17 00:01:15,810 --> 00:01:23,340 just do some letters H and then E and then L just like that we're going to do Hello. 18 00:01:23,340 --> 00:01:24,360 But I ran out of patience. 19 00:01:24,360 --> 00:01:26,010 So instead we're stuck with hell. 20 00:01:26,010 --> 00:01:33,480 And if I hit enter here, what we end up with is the concatenated result of those three text pieces, 21 00:01:33,480 --> 00:01:37,460 those three strings shoved together to form one piece of text. 22 00:01:37,470 --> 00:01:38,790 Now, why would we do that? 23 00:01:38,790 --> 00:01:40,320 We probably wouldn't do it this way. 24 00:01:40,320 --> 00:01:44,220 We wouldn't just go through all this work when I could have just done this right. 25 00:01:44,220 --> 00:01:46,560 Select h e l. 26 00:01:46,590 --> 00:01:51,390 That would have been a lot easier if I had my semicolon and we get the same end result. 27 00:01:51,390 --> 00:01:54,810 But where it does become useful is if we're working with real data. 28 00:01:54,810 --> 00:01:59,850 So we have our author first name or F name and author L name four last name. 29 00:02:00,090 --> 00:02:03,510 Let's combine them together to get an author full name. 30 00:02:03,510 --> 00:02:07,800 So we would concatenate the first name with the last name. 31 00:02:08,220 --> 00:02:10,949 And as I said, what if we wanted full names? 32 00:02:10,949 --> 00:02:17,160 We would use Concatenate and all these built in functions I'm showing you this term function means some 33 00:02:17,160 --> 00:02:24,360 sort of built in repeatable operation and we will always have parentheses after the name of the function. 34 00:02:24,360 --> 00:02:30,660 And then any values that we want to pass in will go inside the friends separated by commas as we've 35 00:02:30,660 --> 00:02:31,560 already seen. 36 00:02:31,560 --> 00:02:35,940 Right H, comma, E, comma, L instead of the parentheses. 37 00:02:35,940 --> 00:02:39,780 And if you miss those parentheses, you're not going to have a good time. 38 00:02:39,780 --> 00:02:41,250 You'll get an error. 39 00:02:42,930 --> 00:02:48,120 As you can see here, it thinks can Cat is a column name, but really it's supposed to be a function 40 00:02:48,120 --> 00:02:48,330 name. 41 00:02:48,330 --> 00:02:51,270 But my school doesn't know that if we don't have the parents. 42 00:02:51,540 --> 00:02:55,080 So we can concatenate one column with another column. 43 00:02:55,080 --> 00:02:56,540 And that's exactly what I want to do. 44 00:02:56,550 --> 00:02:59,850 Can cat author f name with author L name. 45 00:02:59,850 --> 00:03:02,220 So let's try that select. 46 00:03:02,250 --> 00:03:04,040 We still have to do select also, right? 47 00:03:04,050 --> 00:03:08,130 If we just do can cat something like this. 48 00:03:10,720 --> 00:03:11,650 It doesn't work. 49 00:03:11,680 --> 00:03:13,240 That's not valid skill. 50 00:03:13,240 --> 00:03:18,070 We have to have a select in there to get any results, so select can cut. 51 00:03:19,260 --> 00:03:21,090 Author f name. 52 00:03:21,690 --> 00:03:27,750 And let's just start simple and put in a couple of exclamation points at the end from books. 53 00:03:28,980 --> 00:03:29,910 And here we go. 54 00:03:29,940 --> 00:03:35,670 We see Jhumpa, Neal, Dave, Michael, Patty and so on. 55 00:03:35,700 --> 00:03:41,100 So we've concatenated every author's first name with three x exclamation points. 56 00:03:41,100 --> 00:03:42,780 But that's not what I want to do. 57 00:03:42,810 --> 00:03:46,800 I want to instead concatenate the first name with the last name. 58 00:03:47,930 --> 00:03:49,520 Author underscore l name. 59 00:03:49,520 --> 00:03:52,760 Just make sure I have the correct column names and I'll hit enter. 60 00:03:52,850 --> 00:03:54,120 And we're getting close. 61 00:03:54,140 --> 00:04:00,950 We have Jhumpa Lahiri, Neil Gaiman, Dave Eggers, which I mean its first name and last name together. 62 00:04:00,950 --> 00:04:02,180 That's what we said to do. 63 00:04:02,210 --> 00:04:07,250 There's no space, though, and we probably would want a space in there if we were working with full 64 00:04:07,250 --> 00:04:07,830 names. 65 00:04:07,850 --> 00:04:12,440 Now, right now what we're doing is simply going through these string functions and seeing how they 66 00:04:12,440 --> 00:04:13,010 work. 67 00:04:13,040 --> 00:04:15,170 Is this a useful operation? 68 00:04:15,170 --> 00:04:16,010 Maybe. 69 00:04:16,010 --> 00:04:17,070 Maybe not. 70 00:04:17,089 --> 00:04:20,180 Would we need a list of full names just like this? 71 00:04:20,660 --> 00:04:21,380 Who knows? 72 00:04:21,380 --> 00:04:24,110 But for now, let's say that's what our end goal is. 73 00:04:24,110 --> 00:04:27,150 We want a list of full names and nothing else. 74 00:04:27,170 --> 00:04:35,120 Well, what I can do is concatenate author F name, author L name with a space in the middle so I can 75 00:04:35,120 --> 00:04:37,280 put as many things as I want in here. 76 00:04:37,310 --> 00:04:43,670 I can hard code a string, like an empty string or a space between the first name in the last name. 77 00:04:43,670 --> 00:04:47,770 Or I could put another column name in there too if I wanted, but I'm not going to do that. 78 00:04:47,780 --> 00:04:49,070 I'll just do a space. 79 00:04:49,310 --> 00:04:55,130 And now when I hit Enter, we have Jhumpa Lahiri, Dave Eggers, Patti Smith. 80 00:04:55,880 --> 00:05:02,180 One thing also that's a little annoying is that the column name here is really nasty because it's not 81 00:05:02,180 --> 00:05:03,410 an existing column. 82 00:05:03,410 --> 00:05:08,150 It's not as if we just did select author L name. 83 00:05:09,050 --> 00:05:14,050 From books because that is the actual column name and that's what we asked for. 84 00:05:14,060 --> 00:05:18,830 But here we asked for this whole thing for each row. 85 00:05:18,830 --> 00:05:20,960 And so the column name is not. 86 00:05:21,140 --> 00:05:22,220 It's not nice to look at. 87 00:05:22,220 --> 00:05:24,110 And there's a way better name for that. 88 00:05:24,350 --> 00:05:28,130 Why not just full name or author name or something like that? 89 00:05:28,130 --> 00:05:33,890 We know how to rename a column in our results using as we can give it an alias. 90 00:05:33,890 --> 00:05:35,630 So I'll recall that line. 91 00:05:35,630 --> 00:05:44,060 And then right after Mike and Cat, I'll say, as with one s as And then how about just author name 92 00:05:44,750 --> 00:05:48,800 and there we are, we get author name and the same results. 93 00:05:48,800 --> 00:05:54,050 It's just has a different title now instead of contact cat author F Name space author L name. 94 00:05:54,230 --> 00:05:57,410 So that's a basic or a basic usage of content. 95 00:05:57,410 --> 00:06:02,690 It will take whatever values we pass to it, whatever column names or whatever strings or pieces of 96 00:06:02,690 --> 00:06:06,500 text, and combine them to form a single string for each row. 97 00:06:06,500 --> 00:06:07,190 Of course. 98 00:06:07,730 --> 00:06:13,820 So again, author f name with a space author L name would give us Dave Eggers and Jhumpa Lahiri, and 99 00:06:13,820 --> 00:06:18,170 then we can simply select that from a table, which is what we've already done. 100 00:06:18,320 --> 00:06:24,560 But there's another flavor of kin cat, which is kin cat W s and there is an underscore there and it 101 00:06:24,560 --> 00:06:25,970 doesn't stand for whitespace. 102 00:06:25,970 --> 00:06:28,250 It is width separator. 103 00:06:28,520 --> 00:06:30,350 So this works a little differently. 104 00:06:30,350 --> 00:06:31,550 It's the same concept. 105 00:06:31,550 --> 00:06:38,000 It will can cat whatever we tell it to, but we first provide as that first value what we call the first 106 00:06:38,000 --> 00:06:38,810 argument. 107 00:06:38,810 --> 00:06:45,560 We provide a separator and whatever that separator character is will be smushed between every other 108 00:06:45,560 --> 00:06:46,250 value. 109 00:06:46,970 --> 00:06:54,260 So let me show that without even working with a table, let's just do a select can cat underscore W's 110 00:06:54,800 --> 00:06:57,230 and let's do something like an exclamation point. 111 00:06:57,230 --> 00:07:02,120 And then and here I'll do hi fi lol. 112 00:07:02,150 --> 00:07:06,650 I don't know, a couple of strings, so if I select that, what do we end up with? 113 00:07:06,680 --> 00:07:08,720 Hi by lol. 114 00:07:08,720 --> 00:07:10,960 Well lol doesn't have an exclamation point. 115 00:07:10,970 --> 00:07:17,060 It is only going to be inserted that exclamation point between each piece of information, not afterwards 116 00:07:17,060 --> 00:07:18,470 and not before the high. 117 00:07:18,800 --> 00:07:22,220 So if we compare that with just a regular contact. 118 00:07:23,290 --> 00:07:26,920 We end up with all of those pieces of tech smushed together. 119 00:07:27,220 --> 00:07:28,150 Nothing changed. 120 00:07:28,150 --> 00:07:30,310 Just cram them together as is. 121 00:07:30,310 --> 00:07:37,660 But when we add with separator was that first value is the separator character and that is smushed between 122 00:07:37,660 --> 00:07:39,490 each of the subsequent values. 123 00:07:39,490 --> 00:07:42,820 And that's how we get high exclamation by exclamation lol. 124 00:07:44,020 --> 00:07:48,550 So we could use this to do something like create a unique slug. 125 00:07:48,610 --> 00:07:54,910 Basically part of a URL that contains the title, the author F name and the author ll name all separated 126 00:07:54,910 --> 00:07:55,890 by a dash. 127 00:07:55,900 --> 00:07:57,460 So why don't we try that? 128 00:07:57,490 --> 00:08:01,060 We'll do a select and I'll do this on a separate line. 129 00:08:01,060 --> 00:08:04,360 Sure can cat w s. 130 00:08:04,480 --> 00:08:07,750 I want to separate them using a dash or is that a hyphen? 131 00:08:07,750 --> 00:08:14,200 I never remember, but whatever that is, that line character will take the title and then the author 132 00:08:14,200 --> 00:08:17,230 F name and the author ll name. 133 00:08:18,660 --> 00:08:20,340 From books. 134 00:08:21,570 --> 00:08:22,550 And there we go. 135 00:08:22,560 --> 00:08:28,290 We get The Namesake, Jhumpa Lahiri, and there are spaces in our book names, so it doesn't exactly 136 00:08:28,290 --> 00:08:28,830 work. 137 00:08:28,830 --> 00:08:31,980 Right, because this wouldn't be a valid URL. 138 00:08:32,010 --> 00:08:35,909 We will learn how we could possibly replace spaces later on. 139 00:08:36,330 --> 00:08:37,890 But for now, this is fine. 140 00:08:37,890 --> 00:08:44,730 It's a silly example just to show that if we use can we use whatever separator we provide will be inserted 141 00:08:44,730 --> 00:08:47,670 between each of our concatenated pieces of text? 142 00:08:47,940 --> 00:08:48,560 All right. 143 00:08:48,570 --> 00:08:50,010 That can cat and can cat.