Finding the Length of Text2:50 with Andrew Chalkley
You can obtain lengths of pieces of text using the LENGTH() function.
Imagine we're still working on an e-commerce site. 0:00 And the designers come up to us to ask a question. 0:03 They're updating the design on a customer profile page. 0:06 They want to know the length of the largest user name in the database 0:10 to test out if their design works at the extremes of what's in our database. 0:13 We can use the SQL function called length to help us answer this question. 0:19 We have a select statement that only brings back the username 0:25 from our customers table. 0:28 Let's show the username and their length of the username in the report so 0:30 we can verify that it's actually working. 0:34 Remember, a function starts with the name of the function, length, 0:37 a pair of parenthesis and then the value or 0:42 the column name that you want to be transformed. 0:45 Watch, when we run this, we get the username with their lengths. 0:50 Notice here, in the column names that length username is shown. 0:56 That's what happens when you don't alias a result of a function. 1:01 Let's alias this to something that's easier to understand. 1:05 That's better, but what about the longest username? 1:10 What is it? 1:14 What new keywords have we learned that we could use to figure this out? 1:16 We can order by their length in descending order. 1:20 And then limit it to just the longest one. 1:27 When we run it only the record with the longest username is returned. 1:31 There may be more with the same length, but those results are irrelevant. 1:36 All we want to know is the length. 1:40 You're also not limited to using the results of the function 1:43 in the order by criteria. 1:46 Let's say we wanted to find all usernames under the length of seven characters. 1:49 We could modify our query to look like this. 1:54 Where length is less than seven. 1:58 In fact, you don't even need to select it. 2:06 You can simply move it over to the condition like this. 2:10 You may want to do this, because seeing the length doesn't matter, but 2:15 the actual username is what's important. 2:19 You've seen the length function used in several different ways. 2:23 You've seen it used in this select portion of a query. 2:28 We even saw it being used as part of a work condition. 2:31 You can include it anywhere you'd put a value or column name. 2:35 You should start to see how using functions can help answer more questions 2:41 or form an output. 2:45 We're going to explore many more functions throughout this course. 2:46
You need to sign up for Treehouse in order to download course files.Sign up