- 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
Finding the Length of Text
2:50You can obtain lengths of pieces of text using the LENGTH() function.
Imagine we're still working
on an e-commerce site.
0:00
And the designers come up
to us to ask a question.
0:03
They're updating the design
on a customer profile page.
0:06
They want to know the length of
the largest user name in the database
0:10
to test out if their design works at
the extremes of what's in our database.
0:13
We can use the SQL function called
length to help us answer this question.
0:19
We have a select statement that
only brings back the username
0:25
from our customers table.
0:28
Let's show the username and their
length of the username in the report so
0:30
we can verify that it's actually working.
0:34
Remember, a function starts with
the name of the function, length,
0:37
a pair of parenthesis and
then the value or
0:42
the column name that you
want to be transformed.
0:45
Watch, when we run this,
we get the username with their lengths.
0:50
Notice here, in the column names
that length username is shown.
0:56
That's what happens when you don't
alias a result of a function.
1:01
Let's alias this to something
that's easier to understand.
1:05
That's better, but
what about the longest username?
1:10
What is it?
1:14
What new keywords have we learned
that we could use to figure this out?
1:16
We can order by their
length in descending order.
1:20
And then limit it to just the longest one.
1:27
When we run it only the record with
the longest username is returned.
1:31
There may be more with the same length,
but those results are irrelevant.
1:36
All we want to know is the length.
1:40
You're also not limited to using
the results of the function
1:43
in the order by criteria.
1:46
Let's say we wanted to find all usernames
under the length of seven characters.
1:49
We could modify our
query to look like this.
1:54
Where length is less than seven.
1:58
In fact, you don't even need to select it.
2:06
You can simply move it over
to the condition like this.
2:10
You may want to do this, because
seeing the length doesn't matter, but
2:15
the actual username is what's important.
2:19
You've seen the length function
used in several different ways.
2:23
You've seen it used in this
select portion of a query.
2:28
We even saw it being used as
part of a where condition.
2:31
You can include it anywhere you'd
put a value or column name.
2:35
You should start to see how using
functions can help answer more questions
2:41
or form an output.
2:45
We're going to explore many more
functions throughout this course.
2:46