'Combine / Concat columns in one new column in Google BigQuery

Somebody a hint how I can put the values of four columns into a new column in Google BigQuery? Standard SQL Dialect. Not Legacy. If I try a concat (in the select statement or as a subselect) 

SELECT
Concat (visitId,fullVisitorId,visitNumber) as identifier,
...
FROM ...

SELECT     
(SELECT Concat(visitId,fullVisitorId,visitNumber) as identifier FROM ...),
...
FROM ...

I get a error message like: Error: No matching signature for function CONCAT for argument types: INT64, STRING, INT64. Supported signatures: CONCAT(STRING, [STRING, ...]); CONCAT(BYTES, [BYTES, ...]) at [5:3]

It would be great if someone can help me with this. Thanks.

sqlgoogle-bigqueryconcat


Solution 1:[1]

Have you tried casting them before the concat:

SELECT Concat(cast(visitId as string), cast(fullVisitorId as string), cast(visitNumber as string)) as identifier,

I don't know what you want to do with the result. However, it might make more sense to put the values in to an array or a struct. Often, you don't need to combine values into a string, because more complex types are available.

Solution 2:[2]

Instead of CONCAT(column1, column2, column3), you can just use the || which also concatenates the columns:

SELECT column1 || column2 || column3
FROM example_table

See also:
https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#concatenation_operator

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2 Sander van den Oord

Related Questions