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”
Exercise instructions
Select the first 50 characters of
description
with'...'
concatenated on the end where thelength()
of thedescription
is greater than 50 characters. Otherwise just select thedescription
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;