WEBVTT 00:00.180 --> 00:04.820 Next up in this onslaught of string functions is substring. 00:05.030 --> 00:09.530 So substring allows us to select individual part portions of a string. 00:09.690 --> 00:11.140 I only want the first character. 00:11.250 --> 00:15.230 I only want the middle 10 characters or the last character so on. 00:15.480 --> 00:16.590 So the syntax is similar. 00:16.590 --> 00:17.610 Chicken cat. 00:17.910 --> 00:19.640 Here's an example. 00:19.920 --> 00:23.410 In this case we're not working with columns or tables or anything. 00:23.670 --> 00:25.150 We're just working with static data. 00:25.200 --> 00:26.200 Hello world. 00:26.610 --> 00:27.540 And with this will do. 00:27.570 --> 00:28.290 Select. 00:28.440 --> 00:33.860 So there's our selector again and then substring our string function in parentheses. 00:34.320 --> 00:38.820 In this example the first thing we do is tell it here's the string we want to work with. 00:38.820 --> 00:41.170 So that first thing is Hello world. 00:41.460 --> 00:48.260 And then what we're going to do is only print out the substring the portion from 1 to 4. 00:48.630 --> 00:52.020 So those are indices referring to characters. 00:52.500 --> 00:57.260 And in my ask you Will they start at 1 if you're familiar with other programming languages. 00:57.360 --> 00:58.790 They often start at zero. 00:58.860 --> 01:00.070 That's not the case here. 01:00.390 --> 01:02.380 So h is one. 01:02.520 --> 01:09.530 So we go from 1 to 4 to 1 2 3 4 which gives us hell. 01:09.870 --> 01:13.270 I'm sorry that is a bad word to me. 01:13.650 --> 01:16.400 So hello world one before it gives us that. 01:16.410 --> 01:18.910 But that's not the only way of using it. 01:19.620 --> 01:21.140 Here's another example. 01:21.330 --> 01:28.980 Select substring hello world the same starting piece of data and this time we give it is 1 number 1 01:28.980 --> 01:30.040 index 7. 01:30.360 --> 01:37.820 So if we count one two three four five space is six seven is that W.. 01:38.220 --> 01:42.470 You might think it's only going to give us that W but that's not true. 01:42.470 --> 01:45.410 It gives us from that W on to the end. 01:45.540 --> 01:52.020 So if you only pass in one number rather than 2 It starts from whatever that index is and just goes 01:52.020 --> 01:52.880 to the end. 01:52.890 --> 01:54.530 So let's try a couple out. 01:54.660 --> 02:02.700 We'll do a select substring and we'll just do the hello world example that we just saw it will go from 02:02.700 --> 02:04.340 1 to 4. 02:04.350 --> 02:06.660 So we already know the answer here. 02:06.830 --> 02:09.990 Hope there's hell as you can see. 02:10.220 --> 02:11.510 One two three four. 02:11.550 --> 02:12.810 We get help. 02:12.810 --> 02:21.320 So if we change that from let's do three to eight we get a low Oreille. 02:21.390 --> 02:22.630 So that's one way of using it. 02:22.650 --> 02:27.810 And then the next way as we saw is if we just leave that second or third argument off and we just say 02:27.810 --> 02:29.320 hello world comet 3. 02:29.510 --> 02:34.380 It will go from the third character or one to three to the end. 02:34.830 --> 02:42.990 So we get low world or in the case that I showed you seven we get world and there's one more way that 02:42.990 --> 02:47.360 we can use it which is to actually give it a negative number negative index. 02:47.650 --> 02:49.270 So what do you think will happen. 02:49.280 --> 02:51.170 I give it negative 3. 02:51.360 --> 02:52.710 Hello world. 02:53.250 --> 03:00.240 Well if the answer to that we get RLT So it starts from the end of the string and it goes from one negative 03:00.240 --> 03:05.030 one excuse me to negative 2 to negative 3 and includes all of them. 03:05.280 --> 03:06.840 So R L D. 03:07.030 --> 03:10.920 We try that give negative three. 03:12.000 --> 03:21.420 You can see we get R L D and if we did negative 7 would go 1 2 3 4 5 6 7 to this. 03:21.480 --> 03:24.950 Oh and we get 0 world. 03:24.960 --> 03:27.270 So that's how we do it with just static data. 03:27.540 --> 03:30.470 But now let's say we wanted to work with a column. 03:30.720 --> 03:35.030 So in our case let's take the example of title. 03:35.040 --> 03:42.090 So let me just do a select star from actually that's just like title from books. 03:42.600 --> 03:49.800 So we've got a variety of like some titles right short ones like white noise Coraline long ones like 03:50.040 --> 03:52.310 what we talk about when we talk about love stories. 03:52.440 --> 03:54.800 Let's say I wanted to just print out. 03:55.170 --> 03:58.790 I don't know the first 10 characters of every title maybe. 03:58.860 --> 04:02.460 You know you've probably seen that before when you're on a shop and if there's a long title the whole 04:02.460 --> 04:03.450 thing isn't printed out. 04:03.450 --> 04:09.660 There's just you know a couple words and then dot dot dot or read more something like that that you 04:09.660 --> 04:10.420 can click on. 04:10.620 --> 04:12.390 We'll do something similar. 04:12.390 --> 04:19.140 So to limit this what we do let's just take this as an example. 04:19.140 --> 04:28.410 We can do a select substring will give it the whole title and then we only want from 0 to 10 or from 04:28.410 --> 04:32.430 1 to 10 excuse me we could do that. 04:32.430 --> 04:38.220 And this actually here's another error that I considered editing out but I've decided not to because 04:38.670 --> 04:40.860 it is worth taking note of. 04:41.160 --> 04:47.220 The problem is that it thinks that I'm still in a quote because I have this opening quote and this closing 04:47.220 --> 04:47.700 quote. 04:47.770 --> 04:53.280 But in between I have another quote which is actually an apostrophe in this case. 04:53.280 --> 04:59.990 So this is not going to work if I do this I'll just exit out and just ignore that. 05:00.020 --> 05:07.970 But I have to read you the whole line and rather than using single quotes I'll use double quotes like 05:07.970 --> 05:16.600 this where I'm calling from selected stories just like that. 05:16.770 --> 05:17.180 All right. 05:17.220 --> 05:20.250 And then I'll get rid of this other quote I put at the end. 05:21.240 --> 05:24.360 So that is important just to take note of when you're working with strings. 05:24.360 --> 05:28.710 If you have a quotation mark in there you've got to work around it. 05:28.770 --> 05:31.940 So I do this and you can see it give me where I'm. 05:32.340 --> 05:34.970 Yes this is 10 characters maybe isn't long enough. 05:34.980 --> 05:36.660 But we're just going to work with that. 05:36.690 --> 05:41.830 So now to do it to every single title it works just like we did with King cat. 05:41.850 --> 05:54.870 We're going to do a select substring title comma one comment 10 from books and that's it. 05:54.980 --> 05:56.620 And there we go. 05:56.660 --> 06:04.880 Now we have this ugly heading up here so we could do it as again as in let's just say short title like 06:04.880 --> 06:05.400 that. 06:08.690 --> 06:11.380 Now we get this short title blah blah blah. 06:11.570 --> 06:16.440 The names say North Smith American GI a hologram. 06:16.460 --> 06:17.970 Some of these actually work in a circle. 06:17.990 --> 06:18.760 There we go. 06:18.860 --> 06:20.130 Just good. 06:20.330 --> 06:20.980 All right. 06:21.410 --> 06:23.000 So one other thing I'll show you. 06:23.120 --> 06:27.630 There is a shortcut it's a very small shortcut but you can type substrate. 06:27.760 --> 06:29.570 Su B SDR. 06:29.930 --> 06:36.080 I don't use this all that often because I can really remember what the short cut is because in some 06:36.080 --> 06:41.070 languages there'll be substring where it's SU b s t. 06:41.220 --> 06:43.810 There's some I've seen s b s t. 06:43.920 --> 06:48.430 It's just not clear which characters get cut out because it's not just all vowels because we have a 06:48.440 --> 06:55.470 use still anyway so if you would like you can use this I can just show you if you replace that would 06:55.520 --> 07:03.940 you select substring and just change substring to be substrata it does the same exact thing. 07:04.620 --> 07:11.100 But what I also want to show you is that we can combine concat and substring or any of the string operators 07:11.100 --> 07:13.050 or the string functions you'll see. 07:13.200 --> 07:17.470 So let's say I wanted to add a dot dot dot after this. 07:18.150 --> 07:24.570 So it looks like this short title the name say dot dot dot rather than just an abrupt cut off at a dot 07:24.570 --> 07:25.750 dot dot. 07:25.860 --> 07:30.260 So hopefully you can identify what we need to do is first chop our string. 07:30.260 --> 07:36.800 Use a substring 9:59 and then concatenate that with dot dot dot. 07:36.930 --> 07:38.780 So I'll do this in a separate file. 07:39.010 --> 07:46.200 I'll go ahead and make a new file and I'll just call it something silly like code that as well. 07:46.440 --> 07:50.270 So we know we're going to do is select and there's going to be a king cat in there. 07:50.490 --> 07:53.200 And what we're going to concat is the short title. 07:53.230 --> 07:54.690 So again this is wrong. 07:54.690 --> 07:56.740 What I'm typing but I just want to put a placeholder. 07:56.790 --> 08:01.660 The short title with a dot dot dot from books. 08:01.770 --> 08:03.630 So this won't do anything right now. 08:03.870 --> 08:08.540 We need to replace this with how we actually achieve that short title. 08:08.730 --> 08:11.450 And that's going to be with a substring. 08:11.520 --> 08:24.030 So in here we type substring and we want to put title in there comma and we want it to go from 1 to 08:24.030 --> 08:24.820 10. 08:25.290 --> 08:29.080 So this looks like a lot it's pretty ugly to format it a little nicer. 08:29.220 --> 08:33.680 We can do this depends on how intense you want to be about it. 08:33.690 --> 08:35.680 I like to just do it like this here. 08:36.030 --> 08:38.450 So substring is our first argument. 08:38.450 --> 08:40.340 Comma dot dot dot. 08:40.370 --> 08:43.360 The second one from books. 08:43.440 --> 08:46.670 So if you look at it you can see the order things are going to happen. 08:46.920 --> 08:53.500 So substring is going to happen first for every title so that will be the name say or American G. 08:53.850 --> 09:01.110 And that will be concatenated with dot dot dot and that will run for every single book in our database 09:01.380 --> 09:02.700 from books. 09:03.150 --> 09:08.060 So if we save that we could either source the file or just copy and paste it. 09:08.070 --> 09:08.790 So let's do that. 09:08.790 --> 09:15.150 Now we do source book code data as well. 09:15.900 --> 09:16.920 And there we go. 09:17.100 --> 09:21.460 The name say dot dot dot Norse myth dot dot dot dot dot dot dot. 09:21.840 --> 09:31.830 But you see if we get this really hideous title appear so let's put in as we'll call it as short a title 09:33.090 --> 09:36.540 save again rerun it. 09:36.540 --> 09:42.400 Now we get this much much nicer short title the name say dot dot dot and so on. 09:42.810 --> 09:43.100 All right. 09:43.110 --> 09:45.320 So that's all there is to substring. 09:45.690 --> 09:50.060 But the tricky thing in my opinion is just knowing that you can combine them. 09:50.630 --> 09:52.260 So it takes a little bit of getting used to. 09:52.300 --> 09:56.730 But again the way it works is whatever's on the inside first will be evaluated. 09:56.850 --> 10:01.410 So substring will happen first and then that will be concatenated with this. 10:01.410 --> 10:02.350 So that's it. 10:02.430 --> 10:04.460 We have a couple more and then we'll do some challenges.