Concatenation operator in BigQuery

You might have encountered the slightly odd-looking || in SQL before, whether in BigQuery or your other database system. If not yet, it's called the 'concatenation operator' and well, it concatenates things. In fact, it's the ANSI SQL standard concat...