Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Reporting with SQL

Well done!

You have completed Reporting with SQL!

Sign up for Treehouse Back to Library
Preview
Video Player
00:00
00:00
00:00
  1. 2x 2x
  2. 1.75x 1.75x
  3. 1.5x 1.5x
  4. 1.25x 1.25x
  5. 1.1x 1.1x
  6. 1x 1x
  7. 0.75x 0.75x
  8. 0.5x 0.5x
Use Up/Down Arrow keys to increase or decrease volume.

Creating Excerpts From Text

2: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