- What Are Functions? 3:12
- Adding Text Columns Together 3:39
- Single vs Double Quotes
- Concatenating Text 2 objectives
- Finding the Length of Text 2:50
- Getting the Length of a String 1 objective
- Changing the Case of Text Columns 2:45
- Changing Cases of Strings 2 objectives
- Creating Excerpts From Text 2:19
- Creating Excerpts 1 objective
- Replacing Portions of Text 1:58
- Replacing Strings 1 objective
- Practice Session 3:27
- Review: Working with Text 5 questions
Preview
Video Player
00:00
00:00
Creating Excerpts From Text
2:19Wouldn'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