Get Started

Shorten long strings

The description column of evanston311 can be very long. You can get the length of a string with the length() function.

For displaying or quickly reviewing the data, you might want to only display the first few characters. You can use the left() function to get a specified number of characters at the start of each value.

To indicate that more data is available, concatenate '...' to the end of any shortened description. To do this, you can use a CASE WHEN statement to add '...' only when the string length is greater than 50.

Select the first 50 characters of description when description starts with the word "I".

This is a part of the course

“Exploratory Data Analysis in SQL”

View Course

Exercise instructions

  • Select the first 50 characters of description with '...' concatenated on the end where the length() of the description is greater than 50 characters. Otherwise just select the description as is.

  • Select only descriptions that begin with the word 'I' and not the letter 'I'.

    • For example, you would want to select "I like using SQL!", but would not want to select "In this course we use SQL!".

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Select the first 50 chars when length is greater than 50
SELECT CASE WHEN length(___) ___ ___
            THEN ___(___, ___) || ___
       -- otherwise just select description
       ELSE description
       END
  FROM evanston311
 -- limit to descriptions that start with the word I
 WHERE ___ LIKE ___
 ORDER BY description;
Edit and Run Code