Search code examples
sqlgoogle-bigqueryconcatenation

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.

Solution

  • 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.