SQL Serise Part V (Data Cleaning

LEFT & RIGHT (for column)

# choose the col string you want to separate
SELECT LEFT(col, number) AS new_col # col=> string column, number=> separate index position
FROM TABLE;

SELECT RIGHT(col, number) AS new_col
FROM TABLE;

# Sample:
#    if we want to count the `name` start with 'a' character
SELECT SUM(new_name) AS n_name
FROM (SELECT name, CASE WHEN LEFT(name, 1)='a'
                   THEN 1 ELSE 0 END AS new_name 
                   FROM TABLE) AS t1;

POSITION, STRPOS & SUBSTR

# POSITION, STRPOS: provides the position of a string counting from the left
# ATTENTION: both them are case sensitive
POSITION('target_string' IN col)
STRPOS(col, 'target_string')

# If you want separate the string, use LEFT or RIGHT and POSITION or STRPOS
...
LEFT(col, POSITION('target_string' IN col) -1 ) AS new_col # -1 is to substracting the target_string
...

LOWER, UPPER

# force every character in a string to become lowercase(uppercase)
LOWER(col)
UPPER(col)

CONCAT & ||

# CONCAT & ||: combines values from several columns into one column
...
CONCAT(a, 'space mark(or nothing)', b) AS new_col
a || 'space mark(or nothing)' || b AS new_col
...

CAST

# Allows us to change columns from one data type to another
# change float to int:
CAST(25.6 AS int) => 25
# change string to date:
CAST(year || '-' || month || '-' || day AS date) => 2018-08-21

COALESCE

# Returns the first non-null value passed for each row
COALESCE(col, 'Nothing here') AS show_non-null_col => if col is null, then will show 'Nothing here'