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_tableAlternatively the same result could be achieved by:
firstname || ' ' || last_name
Practical Info
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. BYTES or a STRING. 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 DataIf 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
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.