Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Video Player
00:00
00:00
00:00
- 2x 2x
- 1.75x 1.75x
- 1.5x 1.5x
- 1.25x 1.25x
- 1.1x 1.1x
- 1x 1x
- 0.75x 0.75x
- 0.5x 0.5x
Wouldn't it be cool to create excerpts like this...
To create smaller strings from larger piece of text you can use the SUBSTR() funciton or the substring function.'
SELECT SUBSTR(<value or column>, <start>, <length>) FROM <table>;
Cheat Sheets
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up