Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Reporting with SQL

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Changing the Case of Text Columns

2:45 with Andrew Chalkley

There are two functions for changing cases, UPPER() and LOWER(). Let's look how to use them.

Use the UPPER() function to uppercase text.

SELECT UPPER(<value or column>) FROM <table>;

Use the LOWER() function to lowercase text.

SELECT LOWER(<value or column>) FROM <table>;

Cheat Sheets

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up