CONCAT

Definition

The BigQuery CONCAT function allows you to combine (concatenate) one more values into a single result.

Alternatively, you can use the concatenation operator || to achieve the same output.

Alternatively the same result could be achieved by:

CONCAT(value1[, ...])

Practical Info

• All input values to the function or the operator must be BYTES or a data type which can be cast into a STRING. Data types such dates or floats will be converted to a string in the output and do not need to be cast beforehand.  
• The function returns either BYTES or a STRING.
• If any input value is NULL then the function will return NULL as the output.

Common Questions

How do I join a date and some text together in one column?

The CONCAT function will do this automatically, for example:

value1 [|| ...]

If you want to change the format of the date before combining it with other columns you can use the FORMAT_DATE function on the date column first. For example:

SELECT
  CONCAT(first_name, ' ', last_name) AS full_names_by_function
FROM
  (
    SELECT
      'Michael' AS first_name,
      'Scott' AS last_name
    UNION ALL
(    SELECT
      'Pam' AS first_name,
      'Beesly' AS last_name)
    UNION ALL
(    SELECT
      'Kevin' AS first_name,
      'Malone' AS last_name)
  ) AS names_table

Troubleshooting Common Errors

No matching signature for function CONCAT for argument types

This error occurs when one of the input columns to the CONCAT function can't be cast to a STRING or BYTES , for example an array column or struct column.

firstname || ' ' || last_name

SELECT
  CONCAT(Data.TEXT, Data.DATE) AS with_date
FROM
  (
    SELECT
      'The date is: ' AS TEXT,
      CAST('2020-10-01' AS DATE) AS DATE
  ) AS Data

SELECT
  CONCAT(Data.TEXT, FORMAT_DATE('%B', Data.DATE)) AS with_formatted_date
FROM
  (
    SELECT
      'The month is ' AS TEXT,
      CAST('2020-10-01' AS DATE) AS DATE
  ) AS Data