1
00:00:00,490 --> 00:00:01,960
With large bodies of text,

2
00:00:01,960 --> 00:00:05,330
you often don't want to show all
of the text, all of the time.

3
00:00:05,330 --> 00:00:10,610
For example, when I search on
google.com for treehouse sql, you'll

4
00:00:10,610 --> 00:00:16,110
see there's a lot of these ellipses or
three full stops after each other.

5
00:00:16,110 --> 00:00:20,100
This is because search engines want to
provide you with enough information

6
00:00:20,100 --> 00:00:24,340
in a standardized way without overloading
you with too much information.

7
00:00:25,380 --> 00:00:27,920
You don't want information overload.

8
00:00:27,920 --> 00:00:31,800
It standardizes the output and
doesn't clutter up the page.

9
00:00:31,800 --> 00:00:37,310
You may want to do this yourself sometime,
for example, on an archive on a blog,

10
00:00:37,310 --> 00:00:42,010
or the search pages listing product
descriptions on an e-commerce site.

11
00:00:42,010 --> 00:00:43,840
And that's exactly what we're going to do,

12
00:00:44,970 --> 00:00:49,010
search through the product descriptions
and only return an excerpt.

13
00:00:50,010 --> 00:00:55,680
The technique we'll be using is called
getting the substring of a string,

14
00:00:55,680 --> 00:01:00,750
obtaining a smaller piece of
text from a larger piece.

15
00:01:00,750 --> 00:01:03,950
The function we'll be using
is the SUBSTR function.

16
00:01:03,950 --> 00:01:06,500
SUBSTR is short for substring.

17
00:01:06,500 --> 00:01:09,700
The SUBSTR function is
different from other functions

18
00:01:09,700 --> 00:01:12,930
because it requires more
than just the column name.

19
00:01:12,930 --> 00:01:15,430
It requires the start position and

20
00:01:15,430 --> 00:01:18,770
the length,
meaning the length of the substring.

21
00:01:18,770 --> 00:01:22,110
Each of the values
are separated by a comma.

22
00:01:22,110 --> 00:01:24,180
Let's see this in action.

23
00:01:24,180 --> 00:01:28,300
When we run this query in
the SQL Playground, we see the first

24
00:01:28,300 --> 00:01:33,860
30 characters of the product description,
followed by an ellipses.

25
00:01:33,860 --> 00:01:38,360
Let's review this substring
function to see what it's doing.

26
00:01:38,360 --> 00:01:40,900
First we include the description column

27
00:01:40,900 --> 00:01:43,677
because that's what we want to
generate the substring from.

28
00:01:44,790 --> 00:01:48,210
Then, the numeric position of
the character we want to begin

29
00:01:48,210 --> 00:01:49,107
the substring at.

30
00:01:50,190 --> 00:01:54,100
In most circumstances,
it's the first character.

31
00:01:54,100 --> 00:01:57,644
Then the length of the desired substring,
30.

32
00:01:58,860 --> 00:02:04,240
Finally, after the function call,
we have the concatenation of ellipses.

33
00:02:04,240 --> 00:02:06,630
If we alter the 30 to another value and

34
00:02:06,630 --> 00:02:11,650
execute this query, the short description
substring changes with that value.

35
00:02:13,690 --> 00:02:16,330
We'll continue working
with text in fun and

36
00:02:16,330 --> 00:02:18,560
exciting ways after
a short assessment break.