1. Learn
    2. /
  2. Courses
    3. /
  3. Cleaning Data in SQL Server Databases
Connected

Exercise

Unifying strings

Sometimes it's common to find messy strings when having different values for the same thing. Although all of these values can be valid, it is better to unify them to perform better analysis.

You run this query to filter all the airports located in the city of 'Chicago':

SELECT * FROM airports 
WHERE airport_code IN ('ORD', 'MDW')

In the results, you see that there are inconsistent values for 'Chicago' in the airport_city column, with values such as 'ch'. You will treat these inconsistent values by replacing them.

Instructions 1/3

undefined XP
  • 1
    • Replace 'ch' with 'Chicago' - notice how 'Chicago' became 'Chicagoicago'.
  • 2
    • Use CASE to replace 'ch' with 'Chicago' in all the rows that are not 'Chicago'.
    • Do not change airport_city otherwise.
  • 3
    • Unify 'Chicago' and 'ch' to 'CH' by replacing 'Chicago' with 'ch' and converting the output to upper case.