SQL Resources/BigQuery/CONCAT

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.

Syntax

CONCAT(value1[, ...])

or

value1 [|| ...]
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
full_names_by_function
Michael Scott
Pam Beesly
Kevin Malone

Alternatively the same result could be achieved by:

firstname || ' ' || last_name

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:

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
with_date
The date is: 2020-10-01

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(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
with_formatted_date
The month is October

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.

Try a better way to write SQL in BigQuery
Write SQL together with Count.
Try a better way to write SQL in BigQuery
Write SQL together with Count.