Data Cleaning using SQL

String Manipulation

LEFT and RIGHT

The LEFT (and RIGHT) function is used to pull data from the left (and right) side of the string and then present them as a separate string. LEFT and RIGHT are primarily useful when data is well-structured and predictable.

SELECT DISTINCT date "Datetime",
       LEFT(date, 10) "Date"
  FROM tutorial.sf_crime_incidents_2014_01

LENGTH

LENGTH returns the length of a string.

SELECT DISTINCT pd_district,
       LENGTH(pd_district) pd_length
  FROM tutorial.sf_crime_incidents_2014_01

POSITION and STRPOS

POSITION allows you to specify a substring, and provides the position of that substring within another string, counting from the left.

STRPOS is similar, but with slightly different syntax. Note both POSITION and STRPOS are case sensitive.

SELECT address,
       POSITION(' Block of ' IN address) AS break_position,
       STRPOS(address, ' Block of ') AS str_pos_break_position
  FROM tutorial.sf_crime_incidents_2014_01

Combinations

Combinations of the foregoing commands allow users to parse complex inputs.

SELECT location,
       LEFT(RIGHT(location, LENGTH(location) - (STRPOS(location, '('))), (STRPOS(location, ', ')-2)) lat,
       RIGHT(LEFT(location, LENGTH(location)-1), LENGTH(location) - STRPOS(location, ', ')-2) lon
  FROM tutorial.sf_crime_incidents_2014_01

UPPER and LOWER

UPPER and LOWER force the case of a string to be upper or lower, respectively.

SELECT address,
       UPPER(address) AS upper_case,
       LOWER(address) AS lower_case
  FROM tutorial.sf_crime_incidents_2014_01

CONCAT

CONCAT allows users to concatenate strings from multiple columns together into new strings. CONCAT can also be performed using two pipe characters: ||.

SELECT date datetime,
       time,
       LEFT(date, 10) date,
       CONCAT(LEFT(date, 10), ' ', time) AS new_datetime_str,
       LEFT(date, 10) || ' ' || time AS new_datetime_pipes_str
  FROM tutorial.sf_crime_incidents_2014_01

Data Manipulation

CAST

CAST allows users to create new columns with a different data type than the type of the source data. CAST can also be performed using two colon characters: ::.

Note that the cast has resulted in the format changing from MM/DD/YYYY to the SQL standard YYYY/MM/DD. The rename CTE was required because attempting to cast a column named ‘date’ to the ‘date’ type generated an error.

The CAST function is most useful for turning a string into a number or a date. A simple way of converting a number to a string is to perform any kind of string operation on it (LEFT, RIGHT, SUBSTRING, etc) will perform the conversion automatically.

WITH rename AS (
  SELECT distinct date datetime_string
    FROM tutorial.sf_crime_incidents_2014_01
)

SELECT datetime_string,
       LEFT(datetime_string, 10) date_string,
       CAST(LEFT(datetime_string, 10) AS date) date_cast,
       LEFT(datetime_string, 10)::date date_colons
  FROM rename

COALESCE

COALESCE returns the first NULL value in each row. It is useful for replacing NULL values with values of the user’s choice.

SELECT year,
       year_rank,
       "group",
       artist,
       COALESCE(artist, "No Artist") coalesced_artist,
       song_name
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 1959

SELECT COUNT(artist),
       COUNT(COALESCE(artist, 'No Artist')) count_after_coalesce
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 1959