Creating Excerpts From Text2:19 with Andrew Chalkley
Wouldn't it be cool to create excerpts like this...
With large bodies of text, 0:00 you often don't want to show all of the text, all of the time. 0:01 For example, when I search on google.com for treehouse sql, you'll 0:05 see there's a lot of these ellipses or three full stops after each other. 0:10 This is because search engines want to provide you with enough information 0:16 in a standardized way without overloading you with too much information. 0:20 You don't want information overload. 0:25 It standardizes the output and doesn't clutter up the page. 0:27 You may want to do this yourself sometime, for example, on an archive on a blog, 0:31 or the search pages listing product descriptions on an e-commerce site. 0:37 And that's exactly what we're going to do, 0:42 search through the product descriptions and only return an excerpt. 0:44 The technique we'll be using is called getting the substring of a string, 0:50 obtaining a smaller piece of text from a larger piece. 0:55 The function we'll be using is the SUBSTR function. 1:00 SUBSTR is short for substring. 1:03 The SUBSTR function is different from other functions 1:06 because it requires more than just the column name. 1:09 It requires the start position and 1:12 the length, meaning the length of the substring. 1:15 Each of the values are separated by a comma. 1:18 Let's see this in action. 1:22 When we run this query in the SQL Playground, we see the first 1:24 30 characters of the product description, followed by an ellipses. 1:28 Let's review this substring function to see what it's doing. 1:33 First we include the description column 1:38 because that's what we want to generate the substring from. 1:40 Then, the numeric position of the character we want to begin 1:44 the substring at. 1:48 In most circumstances, it's the first character. 1:50 Then the length of the desired substring, 30. 1:54 Finally, after the function call, we have the concatenation of ellipses. 1:58 If we alter the 30 to another value and 2:04 execute this query, the short description substring changes with that value. 2:06 We'll continue working with text in fun and 2:13 exciting ways after a short assessment break. 2:16
You need to sign up for Treehouse in order to download course files.Sign up