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 concatenation operator so in theory it should work across database engines (but it doesn't - for example SQL Server uses + instead for concatenating strings).

In BigQuery, it does the same thing as CONCAT() for STRINGs and ARRAY_CONCAT() for ARRAYs .

SELECT

  [1,2,3] || [4,5,6] AS  concat_array ,

  ARRAY_CONCAT([1,2,3],[4,5,6]) AS  also_concat_array ,

  'Hello ' || 'World' AS concat_string,

  CONCAT('Hello ', 'World') AS also_concat_string