String Concatenation in BigQuery: Comparing CONCAT Function and || Operator

In BigQuery, there are two main ways to concatenate strings: the CONCAT function and the || operator. While both methods yield the same results, there are some differences between them. This article will explain each approach, using a real business case as an example, and discuss their respective advantages and disadvantages.

Real Business Case: URL Generation

Consider the following business requirement. Let’s assume that you need to generate detailed URLs for events in an advertising database. sites.hostname represents the site’s hostname, and page_path represents a specific page path. To generate the complete URL, you need to concatenate https:// with these fields.

To achieve this, you can use two different methods:

Using the || Operator

SELECT 'https://' || sites.hostname || page_path AS full_url FROM your_table

This method concatenates the strings using the || operator. It concatenates https://, sites.hostname, and page_path sequentially to form the final URL. For example, if sites.hostname is example.com and page_path is /home, the result will be https://example.com/home.

Using the CONCAT Function

SELECT CONCAT('https://', sites.hostname, page_path) AS full_url FROM your_table

This method uses the CONCAT function to concatenate strings. It takes multiple arguments at once and joins them into a single string, which also results in https://example.com/home in this example.

Comparing the Advantages and Disadvantages of || Operator and CONCAT Function

1. Conciseness of Expression
   • The || operator is relatively simple and intuitive to use. It makes the code shorter and improves readability when concatenating multiple strings.
   • The CONCAT function, while less intuitive at first glance, is suitable for combining multiple strings at once. It becomes particularly useful when there are many arguments, as parentheses help to clearly separate the elements.
2. Handling NULL Values
   • Both the || operator and the CONCAT function will return NULL if any of the arguments are NULL. Therefore, using functions like COALESCE to handle NULL values is recommended. For example, COALESCE(sites.hostname, '') can be used to replace NULL values with an empty string.
3. Use Cases
   • When concatenating just two strings or performing concatenation operations that are not too complex, the || operator is advantageous, particularly for enhancing code readability.
   • When concatenating multiple strings simultaneously, the CONCAT function is more convenient. It is especially efficient when there are a large number of dynamic arguments to be combined.
4. Code Maintenance
   • The || operator is easy to read and understand, which makes it suitable for simple scenarios. However, if used excessively, it can make the code more complex and harder to maintain.
   • The CONCAT function, with its explicit listing of arguments, is generally easier to maintain when working with long strings or multiple elements that need to be combined.
5. Performance Differences
   • The performance difference between the || operator and the CONCAT function in BigQuery can vary depending on the frequency of concatenation and the size of the data. Generally, the performance difference is not significant, but in scenarios where strings need to be concatenated a very large number of times (e.g., more than 100,000 times), the || operator might perform slightly faster. This is because the || operator directly concatenates strings without the overhead of a function call. However, the performance difference is minor in most typical use cases and may vary based on the test environment and the nature of the data.

Conclusion

When it comes to concatenating strings in BigQuery, the CONCAT function and the || operator can be used interchangeably, depending on the situation. For simple and quick concatenations, the || operator is preferable, while the CONCAT function is better suited for cases where multiple strings need to be clearly concatenated. It is important to carefully consider the advantages and disadvantages of each method to write efficient code that meets business requirements.