1 00:00:00,400 --> 00:00:03,050 Imagine we're still working on an e-commerce site. 2 00:00:03,050 --> 00:00:06,590 And the designers come up to us to ask a question. 3 00:00:06,590 --> 00:00:10,040 They're updating the design on a customer profile page. 4 00:00:10,040 --> 00:00:13,910 They want to know the length of the largest user name in the database 5 00:00:13,910 --> 00:00:18,350 to test out if their design works at the extremes of what's in our database. 6 00:00:19,470 --> 00:00:23,670 We can use the SQL function called length to help us answer this question. 7 00:00:25,190 --> 00:00:28,130 We have a select statement that only brings back the username 8 00:00:28,130 --> 00:00:30,180 from our customers table. 9 00:00:30,180 --> 00:00:34,620 Let's show the username and their length of the username in the report so 10 00:00:34,620 --> 00:00:37,980 we can verify that it's actually working. 11 00:00:37,980 --> 00:00:42,818 Remember, a function starts with the name of the function, length, 12 00:00:42,818 --> 00:00:45,688 a pair of parenthesis and then the value or 13 00:00:45,688 --> 00:00:49,060 the column name that you want to be transformed. 14 00:00:50,070 --> 00:00:55,799 Watch, when we run this, we get the username with their lengths. 15 00:00:56,850 --> 00:01:00,470 Notice here, in the column names that length username is shown. 16 00:01:01,490 --> 00:01:04,600 That's what happens when you don't alias a result of a function. 17 00:01:05,610 --> 00:01:08,990 Let's alias this to something that's easier to understand. 18 00:01:10,740 --> 00:01:14,840 That's better, but what about the longest username? 19 00:01:14,840 --> 00:01:16,170 What is it? 20 00:01:16,170 --> 00:01:20,380 What new keywords have we learned that we could use to figure this out? 21 00:01:20,380 --> 00:01:23,620 We can order by their length in descending order. 22 00:01:27,619 --> 00:01:30,420 And then limit it to just the longest one. 23 00:01:31,540 --> 00:01:36,640 When we run it only the record with the longest username is returned. 24 00:01:36,640 --> 00:01:40,850 There may be more with the same length, but those results are irrelevant. 25 00:01:40,850 --> 00:01:42,570 All we want to know is the length. 26 00:01:43,800 --> 00:01:46,940 You're also not limited to using the results of the function 27 00:01:46,940 --> 00:01:49,300 in the order by criteria. 28 00:01:49,300 --> 00:01:54,710 Let's say we wanted to find all usernames under the length of seven characters. 29 00:01:54,710 --> 00:01:57,490 We could modify our query to look like this. 30 00:01:58,800 --> 00:02:02,910 Where length is less than seven. 31 00:02:06,699 --> 00:02:10,210 In fact, you don't even need to select it. 32 00:02:10,210 --> 00:02:13,970 You can simply move it over to the condition like this. 33 00:02:15,970 --> 00:02:19,981 You may want to do this, because seeing the length doesn't matter, but 34 00:02:19,981 --> 00:02:22,430 the actual username is what's important. 35 00:02:23,490 --> 00:02:28,040 You've seen the length function used in several different ways. 36 00:02:28,040 --> 00:02:31,620 You've seen it used in this select portion of a query. 37 00:02:31,620 --> 00:02:35,360 We even saw it being used as part of a work condition. 38 00:02:35,360 --> 00:02:39,050 You can include it anywhere you'd put a value or column name. 39 00:02:41,070 --> 00:02:45,380 You should start to see how using functions can help answer more questions 40 00:02:45,380 --> 00:02:46,920 or form an output. 41 00:02:46,920 --> 00:02:49,880 We're going to explore many more functions throughout this course.