Chapter 1: Introduction to CONCAT in BigQuery

As a developer, data scientist, or analyst, you'll frequently encounter string manipulations in BigQuery and related SQL platforms. One of the key functions you'll use is CONCAT. This section will offer a concise overview of how the CONCAT function operates.

The CONCAT function allows you to merge one or more values into a single output. To use this function, all input values should be of the BYTES type or data types that can be converted into STRING format. Notably, if any input argument is NULL, the function will return NULL as well. This function is particularly handy when creating new attributes during the data integration process.

For example, consider the following query:

SELECT CONCAT("Con", "Cat") AS Result;

This would yield the following outcome:

Result — Image by Author

Chapter 2: Using CONCAT with UNION

In addition to simple concatenation, CONCAT can also be applied when working with tables and columns connected via a UNION. Here’s how it works:

WITH Employees AS (

SELECT 'Alice' AS first_name, 'Schmidt' AS last_name

UNION ALL

SELECT 'Bob' AS first_name, 'Barker' AS last_name

)

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM Employees;

Alternatively, you can use a different syntax:

SELECT CONCAT(first_name || ' ' || last_name) AS full_name FROM Employees;

Both queries will produce the same output:

Result — Image by Author

Chapter 3: Conclusion

In summary, understanding the CONCAT function is crucial for aggregating data from various columns. It is particularly beneficial in the ETL process, as it minimizes potential errors for users working with tools like Data Studio. By consolidating data into a single column, you can also reduce costs, since only one column needs to be queried instead of multiple ones. This function plays a vital role in both data integration and subsequent analysis.

Chapter 4: Useful Video Tutorials

To deepen your understanding of string functions in BigQuery, check out the following video resources:

A comprehensive guide on Google BigQuery String Functions.

Essential text manipulation functions to merge and split text in BigQuery.