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'
