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
descriptionwith'...'concatenated on the end where thelength()of thedescriptionis greater than 50 characters. Otherwise just select thedescriptionas 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;This exercise is part of the course
Exploratory Data Analysis in SQL
Learn how to explore what's available in a database: the tables, relationships between them, and data stored in them.
Text, or character, data can get messy, but you'll learn how to deal with inconsistencies in case, spacing, and delimiters. Learn how to use a temporary table to recode messy categorical data to standardized values you can count and aggregate. Extract new variables from unstructured text as you explore help requests submitted to the city of Evanston, IL.
Exercise 1: Character data types and common issuesExercise 2: Count the categoriesExercise 3: Spotting character data problemsExercise 4: Cases and spacesExercise 5: TrimmingExercise 6: Exploring unstructured textExercise 7: Splitting and concatenating textExercise 8: Concatenate stringsExercise 9: Split strings on a delimiterExercise 10: Shorten long stringsExercise 11: Strategies for multiple transformationsExercise 12: Create an "other" categoryExercise 13: Group and recode valuesExercise 14: Create a table with indicator variablesWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.