- 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
Changing the Case of Text Columns
2:45There are two functions for changing cases, UPPER() and LOWER(). Let's look how to use them.
There are number of reasons you want
to change the case of the string.
0:00
For example, make all the text in a column
uppercase or all the text lowercase.
0:04
The two most common uses
are to standardize output and
0:10
when searching for values.
0:14
There are two functions for
changing cases.
0:17
Upper for uppercasing values or columns.
0:19
Lower is used for
making lower case strings.
0:23
Let's see them in actions.
0:27
Let's say we wanted to bring up
a customer's records based on their
0:29
email address.
0:33
Let's run this query.
0:34
Notice how the email
address has a capital A.
0:38
That's because like in SQL playground,
it's case insensitive.
0:42
But it's not the case
in other SQL databases.
0:47
What if we didn't know what
SQL database we're using?
0:51
Is there another way we can rewrite
this query with a function?
0:54
Let's remove the like keyword and
switch it out for
0:59
an equality operator then we
can use the lower function
1:02
to transform the values in the email
column to lowercase values.
1:07
We've standardized
the value we're testing.
1:12
When we run this query
in any SQL environment,
1:15
we'll expect to see the same result.
1:19
Searching the email column
with case insensitivity.
1:21
See that the contents of the email
column is still preserved.
1:27
We're only testing
the result of lower email.
1:31
If we wanted to standardize
the output of email addresses,
1:36
we could use lower email in this
select portion of the query.
1:40
You may require to uppercase what a user
enters into a form that's been stored in
1:51
a database.
1:56
In our e-commerce example,
postal codes in the UK,
1:57
the UK equivalent of the US zip
code system are in uppercase.
2:02
Let's modify this query to
search our addresses table.
2:08
We're listing all the UK postcodes.
2:12
We want to plot them on
a map to see if we can see
2:14
where all of our customers are in
the UK to see if there's any hotspots.
2:17
The developer who's mapping the postal
codes on the map says that they require
2:22
all post codes all to be in upper case.
2:27
We can do that with the upper function.
2:30
When we run this query now, we can pass
off this report to the developer plotting.
2:39