2.5 Data Cleaning (Advanced)

2.5.1 Data Cleaning

2.5.1.1 LEFT and RIGHT

It is the same of Excel functions.

SELECT LEFT(2, something) AS lefty_part_of_simething
FROM interesting

The example above will create a new column with the first two, from the left to right, character of something.

SELECT RIGHT(2, something) AS lefty_part_of_simething
FROM interesting

Almost the same, but start from the right to the left.

2.5.1.2 LEN

Returns the string length.

SELECT LEN(something)
FROM interesting

2.5.1.3 POSITION and STRPOS

POSITION will find a pattern in the string and will return the position (from the left to the right).

SELECT POSITION(',', something) /*Looking for a coma*/
FROM interesting

The STRPOS has the same use and same results.

SELECT STRPOS(something, ',') /*Looking for a coma*/
FROM interesting

Both functions are case sensitive.

2.5.1.4 LOWER and UPPER

Converts string into all lower or all upper cases.

SELECT LOWER(something)
FROM interesting

2.5.1.5 CONCAT

Bind/Combine/Concatenate strings (in different) columns into a new column.

Example 1

SELECT CONCAT(first_name, ' ',last_name) AS complete_name /* The ' ' is the space between strings*/
FROM interesting

You can use ||.

Example 2

SELECT first_name || ' ' || last_name AS complete_name /* The ' ' is the space between strings*/
FROM interesting

2.5.1.6 CAST

CAST allow to convert one type to another.

Example 1

SELECT CAST(year || month || day AS date) AS formatted_date
FROM interesting

The same of Example 1, but with a different notation to CAST clause.

Example 2:

SELECT (year || month || day AS date)::date AS formatted_date
FROM interesting

CAST is useful to converter strings into numbers or dates.

2.5.1.7 COALESCE

Converts NULL fields into Zero.

 

---

A work by AH Uyekita

anderson.uyekita[at]gmail.com