WEBVTT 00:00.120 --> 00:00.490 All right. 00:00.540 --> 00:04.930 So we have that out of the way we've seen how to run files using the source command. 00:05.040 --> 00:08.100 And then we actually did that with the sample data that I gave you. 00:08.130 --> 00:12.910 Now we are actually dive into the meat section which is all about string functions. 00:13.170 --> 00:20.250 And the first thing I want to show you is that if you go to Google and we type in my as well string 00:20.250 --> 00:27.870 functions as string functions excuse that and we hit enter here. 00:28.170 --> 00:29.990 You can see I've already clicked on this link. 00:30.090 --> 00:33.800 The Maia's well and reference manual string functions. 00:34.470 --> 00:38.870 I have to say I'm not a huge fan of the documentation of my ask. 00:38.910 --> 00:45.420 Well for one thing I think it's kind of ugly and looks dated but it does have all of the information 00:45.420 --> 00:46.260 you need. 00:46.310 --> 00:51.300 I just don't really like how it's presented but if you look at this giant list here these are all string 00:51.300 --> 00:55.590 functions that we can use and a lot of them are very very specific. 00:55.620 --> 00:57.810 I'm going to show you some of the more. 00:57.870 --> 01:02.640 Well first of all the useful ones and then a couple just fun ones and that will be able to be combined 01:02.640 --> 01:06.750 together for some good exercises but we're not going to take a look at most of these. 01:06.750 --> 01:10.600 Most people don't even know that these are here. 01:10.710 --> 01:16.580 I'd say that's a generalization but in my experience working with mice and people who work with my ask 01:16.580 --> 01:24.750 you out you use maybe five of these on a regular basis and then occasionally you might need to do something 01:24.750 --> 01:26.940 like hey is there a way of doing X. 01:26.970 --> 01:31.350 Let me go check on the documentation and oh look there is a way of doing that. 01:31.350 --> 01:32.490 Perfect. 01:32.870 --> 01:34.550 Or oh no there's not a way. 01:34.620 --> 01:35.310 Great. 01:35.420 --> 01:37.050 What am I going to do next. 01:37.050 --> 01:38.880 So I just want to show you that this is here. 01:39.270 --> 01:43.120 And the first one that we're actually going to take a look at is called Concat. 01:43.170 --> 01:45.870 So what it will do is combine pieces of data. 01:45.960 --> 01:47.280 It's combining strengths. 01:47.310 --> 01:49.710 So here's an example using this book data. 01:50.100 --> 01:54.360 Remember that we have author first name and author last name. 01:54.360 --> 01:57.770 And there are valid reasons for separating that data sometimes. 01:57.780 --> 02:05.100 I mean you might want to sort books by last name and sometimes you may only want the last name in certain 02:05.100 --> 02:07.340 contexts you might not want the first name. 02:07.560 --> 02:11.140 It's not a great example but there are reasons that you would separate this out. 02:11.280 --> 02:15.530 Just like when you're filling out a form online you're signing up for a site. 02:15.720 --> 02:17.700 They usually don't just ask you for your full name. 02:17.850 --> 02:22.950 They ask you for a first and last name but then if they have those pieces of information it's pretty 02:22.950 --> 02:24.380 easy to combine them. 02:24.390 --> 02:25.610 So that's what we're going to see now. 02:25.710 --> 02:30.600 How do we combine these two and get something that says Juba. 02:30.600 --> 02:36.360 Here we are Neil Gaiman or in this case Foster Wallace as the last name which I'm actually not sure 02:36.360 --> 02:41.370 if that qualifies as a last name exactly but David Foster Wallace how do we combine that. 02:41.370 --> 02:42.410 How do we get that. 02:42.440 --> 02:44.500 The answer is we use Concat. 02:44.520 --> 02:47.100 So let me just first show you what we have right now. 02:47.430 --> 02:58.550 Well do a select authored f name author L name from books and that's what we get here. 02:58.830 --> 03:01.240 Just what I had printed out in the slides. 03:01.680 --> 03:05.570 So what we want again is to combine them but we'll take some baby steps. 03:05.820 --> 03:07.330 And here's a slide. 03:07.470 --> 03:08.620 I totally got to have them myself. 03:08.700 --> 03:10.680 What if I want full names. 03:11.550 --> 03:17.850 So concat the way that concat works is that we pass in multiple arguments or pieces of data and it will 03:17.850 --> 03:18.610 combine them. 03:18.690 --> 03:23.910 So whatever we give it in this case I mean if you had columns x y and z this would work but it's really 03:23.910 --> 03:25.690 just supposed to be an example. 03:25.800 --> 03:32.510 More specifically what you can do are things like this concat a column name with another column. 03:32.550 --> 03:33.730 So that's what we'll work on. 03:33.750 --> 03:39.310 We're going to do first name or author name and author L name and combine them together. 03:39.450 --> 03:40.970 And if we do this right here. 03:41.010 --> 03:47.350 So concat author name and author L name what you actually do is just slam them together. 03:47.730 --> 03:49.700 Put them exactly together concatenate them. 03:49.860 --> 03:51.650 So there's no space in between. 03:51.670 --> 03:54.210 So we get Dave Eggers jubilate here. 03:54.690 --> 03:59.940 So there's no you know that's not exactly what we want maybe quite a comma maybe we want last name comma 03:59.970 --> 04:03.810 first name or maybe we want first name space last name. 04:03.810 --> 04:10.050 So there's a way of doing that when we can cat we can pass in a column name and then some other text 04:10.410 --> 04:11.900 and then another column name. 04:11.970 --> 04:17.030 And if we wanted more text it we can keep going on and on and on and it will concatenate everything. 04:17.040 --> 04:20.680 So to add a space it's pretty simple. 04:20.820 --> 04:30.120 Author first name comma and then a space comma author name and that will give us Dave Eggers Juba the 04:30.150 --> 04:30.760 here. 04:31.010 --> 04:32.860 So now that I've shown you the slides. 04:33.060 --> 04:34.460 Let's take a look. 04:34.620 --> 04:40.870 And the first thing I'll show is that often people expect they can just come in here and just type Concat. 04:41.250 --> 04:52.590 But if I just do concat Well author f name comma author L name there's a problem. 04:52.590 --> 04:54.610 Hopefully you can identify what the problem is. 04:54.690 --> 04:57.480 If I hit Enter this is not valid syntax. 04:57.480 --> 05:00.370 First of all it doesn't know what table. 05:00.780 --> 05:02.830 What if we have multiple tables. 05:03.010 --> 05:05.680 Where's Arthur F. name coming from an author name. 05:05.880 --> 05:10.140 And second of all we have to select So we can't just run these willy nilly. 05:10.350 --> 05:11.880 We can't just run them on their own. 05:11.970 --> 05:19.920 We need to run them as a select select concat and then in here I'm actually going to start with something 05:19.920 --> 05:20.330 simple. 05:20.340 --> 05:21.540 I'm not going to use columns. 05:21.540 --> 05:22.790 I'm going to use text. 05:23.130 --> 05:24.810 So I'll just put something like. 05:24.940 --> 05:30.640 Hello comma world. 05:30.720 --> 05:33.240 So this is very trivial but if I just do this. 05:33.240 --> 05:37.140 Select concat Hello world and I hit enter. 05:37.260 --> 05:40.110 You can see it does it for me and it just gives me. 05:40.200 --> 05:41.010 Hello world. 05:41.040 --> 05:42.610 Combined these two things. 05:43.080 --> 05:50.550 So I can do the same thing in outer space and rather than a space with two dots it is easier to see 05:50.830 --> 05:52.030 and we need a comma. 05:52.360 --> 05:54.000 A lot of punctuation going on. 05:54.180 --> 05:58.960 Select concat Hello comma dot dot dot comma world. 05:58.980 --> 06:01.540 It just shoved them all together and we get. 06:01.600 --> 06:03.580 Hello world. 06:04.050 --> 06:08.530 So that's how we can do it with just text which you probably won't be doing. 06:08.790 --> 06:11.300 You'll be doing things like this. 06:11.480 --> 06:20.950 Select and then you can cut a column like author name and then a space and then author L name from books. 06:21.090 --> 06:22.950 So let's try it. 06:22.950 --> 06:27.020 We can copy it now go over here and paste it. 06:27.540 --> 06:34.200 Select and what it will do when we hit enter is just make a new column for us or in our results. 06:34.200 --> 06:37.200 It doesn't change our initial data just to be clear. 06:37.350 --> 06:39.160 Our data looks the same in the database. 06:39.180 --> 06:44.880 This is purely about printing it out but we have this new thing up here with this horrible title concat 06:44.910 --> 06:47.780 author name comma space comma author Al name. 06:48.090 --> 06:50.810 But then in here we have the data that we're looking for. 06:50.910 --> 06:55.560 We have for the theory we have Patti Smith Raymond Carver and so on. 06:56.100 --> 06:59.350 So that's really useful to combine data like that. 06:59.370 --> 07:05.390 Now if you want to get a little fancier Let's rename it so let's give it an as. 07:05.700 --> 07:09.590 And we'll just say as a full name just like that. 07:09.670 --> 07:12.210 Now I get full name up here. 07:12.450 --> 07:14.840 Jim Pilla here in the game and so on. 07:14.880 --> 07:16.200 So that's Concat. 07:16.200 --> 07:21.350 I'll show you a couple more examples just to show you how these string functions work. 07:21.360 --> 07:23.210 You don't have to just do one at a time. 07:23.250 --> 07:24.990 We could do something like this. 07:25.410 --> 07:35.350 Select and will do author first name just on its own author name as first name. 07:37.260 --> 07:51.040 Let's just do first actually as first comma author L name has lapsed and then we'll have a concat author 07:51.430 --> 07:55.660 and let's do it on new line just because it gets a little messy here. 07:55.780 --> 07:58.900 Concat author f names. 07:58.910 --> 08:02.230 This is what we've already seen with this space comma. 08:02.560 --> 08:07.220 Author L name. 08:07.620 --> 08:13.340 And we'll call it full so we'll have three different things printed out the first name under the title 08:13.370 --> 08:15.840 first author L name at last. 08:15.860 --> 08:20.580 And then the full name combined using concat as full. 08:20.750 --> 08:29.180 So then we need to say from where from books now I hit enter and I get this pretty little table first 08:29.270 --> 08:31.080 last and full. 08:31.130 --> 08:34.020 So what I want to show is that you don't have to use it in isolation. 08:34.130 --> 08:37.660 You can combine it with other things and you can see this as we go. 08:37.760 --> 08:44.270 We're not going to just use concat and you don't have to just use one concat per selects you could have 08:44.270 --> 08:50.240 multiple If we wanted to you know I could have full name and then I could have full names comma separated 08:50.240 --> 08:51.020 instead. 08:51.200 --> 08:58.130 And I found to do that you can probably figure this out but rather than just having a space there I'll 08:58.130 --> 09:00.040 just put a comma. 09:00.890 --> 09:04.060 And now I get a comma here. 09:04.430 --> 09:11.330 All right so one more thing about concat is that there's something called concat ws which stands for 09:11.330 --> 09:13.100 concat with separator. 09:13.100 --> 09:19.160 So there might be times where you're concatenating concatenating multiple fields multiple things together 09:19.340 --> 09:24.620 with the same symbol or piece of data stuck in between them. 09:24.620 --> 09:26.830 So there is a shortcut for that. 09:26.870 --> 09:34.280 So if I wanted to have title dash author named dash author last name let's not debate why but if I wanted 09:34.280 --> 09:36.720 to I could do something like this. 09:36.840 --> 09:39.430 Select Concat. 09:39.830 --> 09:42.060 And we have author. 09:42.440 --> 09:51.650 Actually we started with title comma and then we wanted to dash and then author F. name comma and then 09:51.710 --> 09:56.340 another dash comma author L. name just like that. 09:56.720 --> 10:02.440 So it's kind of a lot of her repetitive typing especially if we have more items that we're combining. 10:03.140 --> 10:10.720 But if I just do this year from books you see it does what you expect puts the title dash first. 10:10.730 --> 10:16.460 Last but we can use concat WS and it looks like this. 10:16.670 --> 10:18.620 So it switches things around a bit. 10:18.620 --> 10:22.230 The first thing that we pass in is what we want the separator to be. 10:22.280 --> 10:25.090 So in this case a dash with a space on either side. 10:25.640 --> 10:30.160 And it will go ahead and then insert that in between every other thing we pass in. 10:30.260 --> 10:35.080 So we'll have title dash author first named dash authorize.net. 10:36.050 --> 10:39.820 So now if I pace that in you can see if we get the same result. 10:39.830 --> 10:43.200 Although I added spaces here but it's the same idea. 10:43.310 --> 10:45.980 It's sandwiched every time. 10:45.980 --> 10:50.990 So just worth noting if you ever are in a situation where you're combining lots of data in the same 10:50.990 --> 10:56.200 manner you can use concat ws to save you typing this or whatever your symbol is. 10:56.210 --> 10:57.170 Over and over. 10:57.440 --> 10:59.080 So that's our first string function. 10:59.080 --> 11:07.300 We saw concat and Concat W-S and what they do is concatenate or shove together smash together strings.